Thanks Barb.

I tried that originally, it also took a long time to run because (and I
failed to mention this originally) there are a number of different tables
containing the lookup data and the user tells the code which table to look
in (and what to look for) using a value in column B (with Select Case). This
means I need to evaluate each cell one after the other and can't operate on
the range all at once unfortunately.

Here's the code as it is now (fwiw) - as you can see I'm not much of a
programmer!:
======================
Sub GetWeek1Data()
Application.Calculation = xlCalculationManual

On Error Resume Next

Dim LookupRange
Dim LookupValue
Dim ImportInd
Dim SumRange
Dim BR1Rev, Br2Rev, Br3Rev, Br4Rev, Br5Rev

For Each C In Range("E4:E500")

ImportInd = ActiveSheet.Cells(C.Row, 2).Value

Select Case ImportInd

Case Is = ""

Case Is = "M"

Case Is = "A"

LookupValue = ActiveSheet.Cells(C.Row, 1).Value
LookupRange = Range("Week1")
C.Value = Application.WorksheetFunction.VLookup(LookupValue,
LookupRange, 4, False)

Case Is = "R"

LookupValue = CStr(Range("Branch1").Value &
Left(ActiveSheet.Cells(C.Row, 1).Value, 6) & ActiveSheet.Cells(2,
C.Column).Value)
LookupRange = Range("REVTABLE")
BR1Rev = Application.WorksheetFunction.VLookup(LookupValue,
LookupRange, 5, False) * -1

If Range("Branch2").Value = "" Then
GoTo cont
Else
LookupValue = CStr(Range("Branch2").Value &
Left(ActiveSheet.Cells(C.Row, 1).Value, 6) & ActiveSheet.Cells(2,
C.Column).Value)
LookupRange = Range("REVTABLE")
Br2Rev = Application.WorksheetFunction.VLookup(LookupValue,
LookupRange, 5, False) * -1
End If

If Range("Branch3").Value = "" Then
GoTo cont
Else
LookupValue = CStr(Range("Branch3").Value &
Left(ActiveSheet.Cells(C.Row, 1).Value, 6) & ActiveSheet.Cells(2,
C.Column).Value)
LookupRange = Range("REVTABLE")
Br3Rev = Application.WorksheetFunction.VLookup(LookupValue,
LookupRange, 5, False) * -1
End If

If Range("Branch4").Value = "" Then
GoTo cont
Else
LookupValue = CStr(Range("Branch4").Value &
Left(ActiveSheet.Cells(C.Row, 1).Value, 6) & ActiveSheet.Cells(2,
C.Column).Value)
LookupRange = Range("REVTABLE")
Br4Rev = Application.WorksheetFunction.VLookup(LookupValue,
LookupRange, 5, False) * -1
End If

If Range("Branch5").Value = "" Then
GoTo cont
Else
LookupValue = CStr(Range("Branch5").Value &
Left(ActiveSheet.Cells(C.Row, 1).Value, 6) & ActiveSheet.Cells(2,
C.Column).Value)
LookupRange = Range("REVTABLE")
Br5Rev = Application.WorksheetFunction.VLookup(LookupValue,
LookupRange, 5, False) * -1
End If

cont:
C.Value = BR1Rev + Br2Rev + Br3Rev + Br4Rev + Br5Rev

BR1Rev = Nothing
Br2Rev = Nothing
Br3Rev = Nothing
Br4Rev = Nothing
Br5Rev = Nothing


Case Is = "P"

C.Value = Cells(C.Row, 3).Value

Case Is = "E"
' "E" is used at the end of the list to stop the for..next

Application.Calculate
Application.Calculation = xlCalculationAutomatic

Exit Sub

Case Else

End Select

Next C

End Sub
====================
cheers

Damien

"Barb Reinhardt" wrote in message
...
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