newparadigmz
asked on
Excel VBA, Can you use Index, Match in VBA without calling Worksheet.Function?
I need to loop through an array, do an index match for tens of thousands of records and the call to worksheet.function is extremely slow. Any way to avoid the slowness or the call altogether?
Set rTradeId = .Range("B2:B" & lRowsEag)
avTradeId = rTradeId.Value2
Set rEntity = .Range("C2:C" & lRowsEag)
rEntity.ClearContents
avEntity = rEntity.Value2
On Error Resume Next
With WorksheetFunction
For j = 1 To UBound(avEntity)
avEntity(j, 1) = .Index(rTableLch, .Match(avTradeId(j, 1), rShAdmin, 0), 1)
Next j
End With
On Error GoTo 0
rEntity.Value2 = avEntity
Set rTradeId = .Range("B2:B" & lRowsEag)
avTradeId = rTradeId.Value2
Set rEntity = .Range("C2:C" & lRowsEag)
rEntity.ClearContents
avEntity = rEntity.Value2
On Error Resume Next
With WorksheetFunction
For j = 1 To UBound(avEntity)
avEntity(j, 1) = .Index(rTableLch, .Match(avTradeId(j, 1), rShAdmin, 0), 1)
Next j
End With
On Error GoTo 0
rEntity.Value2 = avEntity
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks