Nonsense, c is a range and he is picking up the range object and setting its
value property, that is legitimate.

Damien,

Not sure how much difference, but this might be somewhat faster. On
specifics, should you be setting it back to Automatic calculation on a value
of E, it will stay auto thereafter. I would have thought you do that after
all cells are processed.


Sub GetWeek1Data()
Dim c As Range
Dim LookupRange As Range
Dim LookupValue
Dim LookupRevTable As Range
Dim ImportInd
Dim BR1Rev As Double
Dim Br2Rev As Double
Dim Br3Rev As Double
Dim Br4Rev As Double
Dim Br5Rev As Double

Application.Calculation = xlCalculationManual

On Error Resume Next

With ActiveSheet

Set LookupRevTable = .Range("REVTABLE")

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

ImportInd = .Cells(c.Row, 2).Value

Select Case ImportInd

Case Is = "A"

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

Case Is = "R"

LookupValue = CStr(.Range("Branch1").Value & _
Left(.Cells(c.Row, 1).Value, 6) & _
.Cells(2, c.Column).Value)
BR1Rev = Application.VLookup(LookupValue,
LookupRevTable, 5, False) * -1

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

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

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

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

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

BR1Rev = 0: Br2Rev = 0: Br3Rev = 0: Br4Rev = 0: Br5Rev
= 0
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 With

End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Joel" wrote in message
...
You r code is probably running slow becaue you are reusing the variable C
in
two different cases

first here
For Each c In ActiveSheet.Range("E4:E500")

and then again

C.Value = Application.WorksheetFunction.VLookup(LookupValue,
LookupRange, 4, False)


Inside a "for each" loop you can't change the value of the index (in this
case C).


"Damien McBain" wrote:

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