Can't find Excel VBA formula type mismatch error

excel, excel-formula, vba

Solution

First of all, writting a formula like that is a really bad idea, for three main reasons:

- It's virtually impossible to understand

- It's virtually impossible to debug

- It it repeats many calculations = inefficient

Refactoring, it becomes

With WorksheetFunction
    A = .Max(deadRange)
    B = .CountIf(deadRange, " >= " & (A / 2))
    C = .CountIf(deadRange, " <= " & (A / 2))
    D = .Index(deadRange, B)
    E = .Index(deadRange, C)
    F = .Lookup(D, deadRange, concRange)
    G = .Lookup(E, deadRange, concRange)

    analysisSheet.Cells(ff, 14).Value = _
        F - ((D - (A / 2)) * (F - G) / (D - E))
End With

Debugging this reveals the problem: the `spaces` around `>=` in `.CountIf(deadRange, " >= " & (A / 2))` is the cause of the error.

So, use instead

    B = .CountIf(deadRange, ">=" & (A / 2))
    C = .CountIf(deadRange, "<=" & (A / 2))

Problem

I am getting type mismatch 13 error and I can't see where it is. Here is the code. ``` Set concRange = Range(Cells(11, 48), Cells((10 + noDilutions), 48)) Set deadRange = Range(Cells(11, 49), Cells((10 + noDilutions), 49)) analysisSheet.Cells(f, 14).Value = _ (WorksheetFunction.Lookup(WorksheetFunction.Index(deadRange, _ WorksheetFunction.CountIf(deadRange, " >= " & _ (WorksheetFunction.Max(deadRange) / 2))), deadRange, concRange)) _ - (((WorksheetFunction.Index(deadRange, WorksheetFunction.CountIf _ (deadRange, " >= " & (WorksheetFunction.Max(deadRange) / 2)))) _ - (WorksheetFunction.Max(deadRange) / 2)) * ((WorksheetFunction.Lookup _ (WorksheetFunction.Index(deadRange, WorksheetFunction.CountIf(deadRange, _ " >= " & (WorksheetFunction.Max(deadRange) / 2))), deadRange, concRange)) _ - (WorksheetFunction.Lookup(WorksheetFunction.Index(deadRange, _ WorksheetFunction.CountIf(deadRange, " <= " & _ (WorksheetFunction.Max(deadRange) / 2))), deadRange, concRange))) _ / ((WorksheetFunction.Index(deadRange, WorksheetFunction.CountIf(deadRange, _ " >= " & (WorksheetFunction.Max(deadRange) / 2)))) _ - (WorksheetFunction.Index(deadRange, WorksheetFunction.CountIf _ (deadRange, " <= " & (WorksheetFunction.Max(deadRange) / 2)))))) ``` I've tried changing "WorksheetFunction" to "Application" and "Application.WorksheetFunction". Yes it is a very very long formula :D Can anyone help me out?

Original source