Problem with Index Loop

AndyD

Active Member
Joined
Nov 14, 2002
Messages
449
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

inarbeth

Well-known Member
Joined
Apr 8, 2002
Messages
912
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

Active Member
Joined
Nov 14, 2002
Messages
449
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

Well-known Member
Joined
Feb 18, 2002
Messages
1,143
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

Well-known Member
Joined
Apr 8, 2002
Messages
912
An addendum. You need to reselect B1 before running the macro to avoid an error.
 
L

Legacy 21301

Guest
AndyD said:
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 :-

Code:
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 ;-

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

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,560
Messages
5,765,100
Members
425,258
Latest member
brentmitchell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top