Another alternate would be to define a range of the cells you are using for
the for/next and do something like this

Dim myRange as range
Set myRange = Range("B2:B400")
myRange.FormulaR1C1 = "=vlookup(..."

'Calculate the sheet
myRange.parent.calculate
'If you want the values, instead of the function
myRange.value = myrange.value
--
HTH,
Barb Reinhardt



"Damien McBain" wrote:

Hi

I have a sub which uses a For...Next loop to populate the cells in a
column.based on the contents of column A in the same row.

I presently use WorksheetFunction.VLookup to get the value from another
worksheet. Is there a faster way to achieve the same result, maybe without
calling a worksheet function? The code takes some time to run through about
400 cells. I have auto calculation turned off during execution.

TIA

Damien