# How to implement MATCH and INDEX functions together in Excel VBA?

I am trying to find the row number of the first cell that is less than zero. So, by browsing through the net I was able to come up with this formula which works perfectly fine:

``````=MATCH(TRUE,INDEX(\$C2531:\$C2731 < 1,0),0)
``````

But I would like to implement this in excel VBA and I tried few options and I get type mismatch error 13, Can some one help me on this?

``````With Application.WorksheetFunction
Range("C2527").Value = .Match(True, .Index(Range("C2531:C2731") < 1, 0), 0)
End With
``````

-

As far as I understand, your are using the intersection operator (space) in your INDEX formula: "\$C2531:\$C2731 <1". This will compare every cell between in one range (\$C2531\$C2731) against another range, but in this case a "<1" comparison instead. So the Intersection will return a range containing a TRUE or FALSE value for each comparison (ie. an array formula). MATCH will then look at that resulting range for "TRUE" values.

In other words, the formula is relatively complex behind the scenes, and although it is short to read, in VBA you want to keep things as simple and clear as possible from a "behind the scenes" perspective - or you'll run into problems. What you are trying to achieve is simple, yet cramming INDEX and MATCH functions into VBA is not.

In Visual Basic, there's no native intersection operator. From what I can find, the only equivalent to the is the Application.Intersect function, which takes Range objects as parameters. Unfortunately, although you can create the first range as "Set myRange = Range("\$C2531:\$C2731"), I have not been able to create a Range object of "<1" in VBA, as this is not a valid cell range. So it seems the Intersection Operator in Excel Formulas is more capable/flexible than the Application.Intersect method in VBA. And that's the point of failure here ("\$C2531:\$C2731 <1" is not valid VBA)

Now you could spend a day trying to see if you can manage to intersect a range and a "<1" in VBA, but there are simpler ways to do this in VBA. If it was me, I would not use Worksheet functions, and just use a standard For...Next loop to go over your rows and do the comparison in plain visual basic for each cell in your Range. If one comparison match, return the row number.

To get an idea, it would look something like:

``````Dim myRange As Range
Set myRange = Range("\$C2531:\$C2731")
For y = 1 to myRange.Cells.Rows.Count
If y < 0 Then
result = myRange.Cells(y,1).Row
End
Next y
``````
-
Thanks mtone, I understood the complexity of the function. So I used the simple FOR NEXT Loop and I could get to the answer I was looking for. The modified code is Dim Range1 As Range Set Range1 = Range("\$C2531:\$C2731") For Y = 1 To Range1.Cells.Rows.Count I = Range("C2531").Offset(Y - 1, 0).Value If I < 1 Then Rownum = Y GoTo Loop1end: End If Next Y Loop1end: – NK1 Apr 24 '13 at 14:20

You can simply send the forumla as a string. Below is an example.

``````Range("C2527").Value = "=MATCH(TRUE,INDEX(\$C2531:\$C2731 < 1,0),0)"
``````
-
Thanks Santosh, That was the function I wrote but I was looking for help to represent this functionality in VBA. – NK1 Apr 24 '13 at 14:23