VBA Array Index Match

Question

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...

user6585761 · Accepted Answer · 2020-11-14 17:33:01Z

1

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.

answered Nov 14, 2020 at 17:33

user6585761

Sign up to request clarification or add additional context in comments.

3 Comments

Andres Cobos Over a year ago

How about when I have 2 different ranges? Now I want to find the value and return the time from column B With Application.WorksheetFunction TIME235 = .Index(ActiveWorkbook.Sheets(1).Range("$B$25:$B$" & LastRow), .Match(TEMP235, ActiveWorkbook.Sheets(1).Range("$" & TCColLet & "$25:$" & TCColLet & "$" & LastRow), 0)) End With

Andres Cobos Over a year ago

The previous one worked like charm BTW, thank you very much mate!

user6585761 Over a year ago

It looks like your lookup value for Match is incorrect. Shouldn't it be .Match(TEMP215, ...?

Collectives™ on Stack Overflow

VBA Array Index Match

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related