Problem with Index Loop

[AndyD]

Hi
We've thousands of records using the index look-up formula
I thought i could save time by using a loop without updating the screen....however this takes longer than copying an index formula down the range...any ideas as to why this might be?



Sub macro_1()

Application.ScreenUpdating = False

Dim i As Integer

For i = 6 To 25000

Cells(i, 3).Select
ActiveCell.FormulaR1C1 = _
"=INDEX(R3C[2]:R65000C[3],MATCH(RC[-1],R3C[2]:R65000C[2],0),2)"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Next i

End Sub

 

[inarbeth]

Can't say why but I tried this macro for a lookup rather than index. I put the formula in cell B1 and then ran this macro:
Sub Macro1()

'
Selection.AutoFill Destination:=Columns("B:B"), Type:=xlFillDefault
Columns("B:B").Select

End Sub

This ran almost instantly.

 

[AndyD]

Hiya thanks very much for your reply

quick question: does a lookup formula find a near match if cant find an exact match (as we need to always find exact match)

also we have up to 65,000 records to lookup/index about 40,000 records so can take a while...

thanks
Andy

 

[WillR]

You can specify whether to find an exact match or the closest match

Placing FALSE in a VLOOKUP formula will specify an exact match - an error value will be returned if a match is not found...

similarly, placing a 0 (zero) in a MATCH formula will amount to the same...

See the excel help files for details of the various formula syntaxs

 

[inarbeth]

An addendum. You need to reselect B1 before running the macro to avoid an error.

 

[Legacy 21301]

Hi
We've thousands of records using the index look-up formula
I thought i could save time by using a loop without updating the screen....however this takes longer than copying an index formula down the range...any ideas as to why this might be?

Because you are selecting a cell and copying the formula 24,995 times, versus selecting and copying once.

In any event, macro or not, filling so many cells with an INDEX formula is not quick since INDEX is a volatile function and the formulas get re-calculated whenever the worbook is re-calculated.

As has been suggested, you could use VLOOKUP instead.
If you need a macro :-

Sub macro_1()
[C6:C25000].FormulaR1C1 = "=VLOOKUP(RC[-1],R3C[2]:R65000C[3],2,0)"
End Sub

Or, to restrict it to the used cells in column B ;-

Sub macro_1()
Range([C6], [B65536].End(xlUp)(1, 2)).FormulaR1C1 = "=VLOOKUP(RC[-1],R3C[2]:R65000C[3],2,0)"
End Sub