excel VBA index and match functions

0
votes

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

2
excelworksheet-functionvba
you are using MATCH(0.01,H$2:H$10) , it's looking for value 0.01, not values that are none-zero, if a cell equals 0.2 your Match will no return it. You need to change your Match to MATCH(0.01,H$2:H$10,-1), the third parameter equals to -1 means it's looking for values greater than 0.01 – Shai Rado

2 Answers

1
votes

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
1
votes

I think once you want to use VBA, you need to use the VBA added capabilities, and not stick with the formula you constructed in Excel.

Since, you are looking for the first cell in Column H with a none-zero value, you can easily find it using the Application.Match, but you need to set the third parameter of match to -1 (means Greater than, looking for a match for values > 0.01).

So now, we have the row number, if you want to find the value in Column X for this row, you can use Range("AC2").Value = Range("X" & MatchRow + Rng.Item(0).Row).Value

Code 

Option Explicit

Sub ConvertFormulaToVBA()

Dim MatchRow As Variant
Dim Rng     As Range
Dim lengthRows As Long, i As Long

lengthRows = Cells(Rows.Count, "H").End(xlUp).Row '<-- get last row with data in Column H (in your example it's 10)
Set Rng = Range("H2:H" & lengthRows) ' <-- set the range to H2 until last row in Column H        

MatchRow = Application.Match(0.01, Rng, -1) ' <-- setting the third parameter to -1, meaning greater than 0.01
If Not IsError(MatchRow) Then
    Range("AC2").Value = Range("X" & MatchRow + Rng.Item(0).Row).Value
Else
    ' raise a message box if there is no Match
    MsgBox "No none-zero value found at Range " & Rng.Address
End If

End Sub