CODEWITHSUNDEEP
arraysexcelvba
Andres Cobos
Andres Cobos

Reputation: 43

VBA Array Index Match

I've been trying to run an array formula to return a variable.

On column B I have dates & times (mm/dd/yyyy hh:mm) and column C I have temperatures. I want to look for a value greater than or equal to 215 on the temperature column "C".

Dim TEMP215 As Variant
With Application.WorksheetFunction
TEMP215 = .Index(ActiveWorkbook.Sheets(1).Range("C2:C460"), .Match(True, ActiveWorkbook.Sheets(1).Range("C2:C460") >= 215, 0))
End With

If I write the formula just like that in a cell it works perfectly, running it with Ctrl + Shift + Enter. I have tried the TEMP215 as variant, long, integer, double, single... pretty much everything without success...

Upvotes: 0

Views: 127

Answers (1)

Domenic
Domenic

Reputation: 8104

Try using the Evaluate method...

Dim TEMP215 As Variant

With ActiveWorkbook.Sheets(1).Range("C2:C460")
    TEMP215 = Evaluate("INDEX(" & .Address(External:=True) & ",MATCH(TRUE," & .Address(External:=True) & ">=215,0))")
End With

Note that the Evaluate method has a 255 character limit.

Upvotes: 1

Related Questions