Referencing range name in calculation
 

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.

Referencing range name in calculation
 

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!!
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"Bob" wrote:

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.

Referencing range name in calculation
 

Bob,

Try it like this:

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


--
Hope that helps.

Vergel Adriano


"Bob" wrote:

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.

Referencing range name in calculation
 

Bob,

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

If the formula is correct, it should work.


"Bob" wrote:

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.

Referencing range name in calculation
 

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!!

--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"Bob" wrote:

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.

Referencing range name in calculation
 

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


"Bob" wrote:

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.