Referencing range name in calculation

[Bob]

I have the following three lines of code:

Dim a As Single
a = Worksheets(Sheet1Name).Cells(Sheet1RowPointer, h).Value
a = Round(a * (1 + WorksheetFunction.VLookup(Cells(Sheet1RowPointer, _
Sheet1RespCodeColPointer), "OverheadLookup", 2)), 0)

I'm trying to perform a lookup using the range name "OverheadLookup" but I
get an error message when line 3 executes.

Any help would be greatly appreciated. Thanks.

 

[Pranav Vaidya]

Hi Bob,

I think the problem is with closing brackets-
It should be like this

a = Round(a * (1 + WorksheetFunction.VLookup(Cells(Sheet1RowPointer, _
Sheet1RespCodeColPointer), "OverheadLookup", 2, 0)))

Hope this helps!!

 

[Vergel Adriano]

Bob,

Try it like this:

a = Round(a * (1 + WorksheetFunction.VLookup(Cells(Sheet1RowPointer, _
Sheet1RespCodeColPointer), Range("OverheadLookup"), 2)), 0)

 

[Cavy]

Bob,

Try changing "OverheadLookup" for Range("OverheadLookup")

If the formula is correct, it should work.

 

[Pranav Vaidya]

Hi Bob,
There are 2 problems one with the closing brackets and one with the 'cells'.
Your formula should be like this-

a = Round(a * (1 + WorksheetFunction.VLookup(Cells(Sheet1RowPointer, _
Sheet1RespCodeColPointer).Value, "OverheadLookup", 2, 0)))

Hope this helps!!

 

[Bob]

Thanks to everyone for your helpful suggestions. In addition to omitting
"Range", I realized that "Cells(Sheet1RowPointer, Sheet1RespCodeColPointer)"
references a string (rather than a value). So the solution is as follows:

Dim RespCode As String
Dim a As Single

RespCode = Worksheets(Sheet1Name).Cells(Sheet1RowPointer, _
Sheet1RespCodeColPointer)

a = Worksheets(Sheet1Name).Cells(Sheet1RowPointer, h).Value

a = Round(a * (1 + WorksheetFunction.VLookup(RespCode, _
Range("OverheadLookup"), 2)), 0)

Thanks again,
Bob