excel VBA index and match functions

Question

I am trying to change excel functions into vba code. the formula below in Col AC, Row 2... =IF(ROWS($1:1)< MATCH(0.01,H$2:H$10)+1,"",INDEX(X:X,ROWS($1:1)-MATCH(0.01,H$2:H$10)+1))

...scans the first 10 rows of Col H.

This formula looks for the first none-zero value in the rows of Col H. When it finds that row, then the values in col X will be printed out in Col AC so that the row in Col AC matches the row with the first non-zero value in Col H.

I hope that description makes sense. It works perfectly in excel worksheet. Now, i would like to change it into VBA code, here is what I have...

For i = 2 To lengthRows
    With Application.WorksheetFunction    
        Range("AC" & i) = .IF(Rows(1) < .Match(0.01, Range("H2:H10")) + 1, "", .Index(Columns(24), Rows(1) - .Match(0.01, Range("H2:H10")) + 1))    
    End With
Next i

...Rows(1) is the first row and Columns(24) is Col X.

When I run the code, I am getting a run-time error mismatch '13: Type mismatch.

I am trying to understand how this previous question was answered: Excel VBA: how to solve Index and Match function type mismatch error

Answer 1

Taking the example from the previous answered question. You're pushing the match result into the index formula. If the match result doesn't find a match then it'll return Error 2042, which when pushed into the Index formula gives the mismatch error.

To adapt that solution for your example would be as follows:

Dim rw As Variant
With Application.WorksheetFunction
    For i = 2 To lengthRows
            rw = .Match(0.01, Range("H2:H10")) 'Is there a reason you're not specifying the third parameter 0 for exact match?
            If Not IsError(rw) Then
                Range("AC" & i) = .If(Rows(1) < .Match(0.01, Range("H2:H10")) + 1, "", .Index(Columns(24), Rows(1) - .Match(0.01, Range("H2:H10")) + 1))
            Else
            ' Do something else if there was an error

            End If
    Next i
End With