Vlookup #NAME error using vba

[billyheanue]

Hi everyone,

I want to use variables I declared in the actual formula of the print cell. For instance,
this code works fine without errors - HOWEVER, I want to use variables "rownum" and "colnum" as cell coordinates to find the lookup value, i.e the first argument in the vlookup equation.

Private Sub CommandButton8_Click()
Dim rownum As Long
Dim colnum As Long
Dim x As Long
Dim y As Long
Dim colindexval As Double
Dim resizeval As Double


rownum = Sheet1.Cells(28, 21).Value
colnum = Sheet1.Cells(27, 21).Value
x = Sheet1.Cells(20, 21).Value
y = Sheet1.Cells(21, 21).Value
resizeval = Sheet1.Cells(19, 12).Value
colindexval = Sheet1.Cells(16, 12).Value
Sheet9.Cells(y, x).Resize(resizeval, 1).Formula = "=VLOOKUP(Sheet9.Cells(rownum,colnum),Sheet8!" _ ' these are the lines that need addressing
    & Sheets("Price Data").Range("A12").CurrentRegion.Address & ",Sheet1!L$29,FALSE)" ' the current region bit is working fine
    
End Sub

What literally ends up put in the cell:
" =VLOOKUP(Sheet9.Cells(rownum,colnum),Sheet8!$A$11:$I$44,Sheet1!L$29,FALSE) "
^which ends up being a #NAME error

How do I fix this so the excel cell terminology recognizes code terminology? I.e, "=vlookup(Sheet9.Cells(rownum,colnum),Sheet8! ... "

I want the vlookup value to be the cell on Sheet 9 given by the coordinates [rownum,colnum] - and not have the formula literally say that!

Thank you Everyone!
Billy

 

[JackDanIce]

Try:

Dim fVLOOKUP as String

fVLOOKUP = "=VLOOKUP(@1,@2,@3,FALSE)"

fVLOOKUP = Replace(fVLOOKUP, "@1", Sheet9.Cells(rownum,column).Value)
fVLOOKUP = Replace(fVLOOKUP, "@2", Sheets("Price Data").Range("A12").CurrentRegion.Address)
fVLOOKUP = Replace(fVLOOKUP, "@3", Sheets("Sheet1").Range("L29").Value)

Sheet9.Cells(y, x).Resize(resizeval, 1).Formula = fVLOOKUP

 

[RoryA]

Assuming the Sheet8 in the formula shoudl really refer to Price Data, I think you want:

Sheet9.Cells(y, x).Resize(resizeval, 1).Formula = "=VLOOKUP(" & Cells(rownum, colnum).Address(0, 0) & ",'Price Data'!" _
    & Sheets("Price Data").Range("A12").CurrentRegion.Address & ",Sheet1!L$29,FALSE)"

 

[billyheanue]

thanks Rory and JackDanIce,

I dont get the whole bit about using the notation: " & ..... & ", what advantage does this have? It seems confusing to me

 

[JackDanIce]

Run this bit of code:

Sub Macro1

Dim str as String
str = "This is a "

Msgbox str
Msgbox str & "elephant"

End Sub

 

[RoryA]

what advantage does this have?

Besides the fact it works?

You have to evaluate the variables and concatenate them into the formula string, otherwise your string contains the literal code, not the resulting values.

BTW, it's easier to use the address of the lookup cell, otherwise you have to test the variable type and enclose it in quotes if it's text, but not if it's a number.

 

[billyheanue]

Besides the fact it works?

You have to evaluate the variables and concatenate them into the formula string, otherwise your string contains the literal code, not the resulting values.

BTW, it's easier to use the address of the lookup cell, otherwise you have to test the variable type and enclose it in quotes if it's text, but not if it's a number.

Hi Rory,
The variables colnum and rownum represent values (numbers, like 22 and 18), but the literal name of the variable is text? What does testing the variable type mean?

 

[RoryA]

Yes. If you write:

"=VLOOKUP(Sheet9.Cells(rownum,colnum),Sheet8!"

the compiler has no way of knowing that you want part of that string to be evaluated first. To the compiler it's just a bit of text like "This is a bit of text". To tell it to evaluate part of it, you need that part to be outside the quotes, and then you need to concatenate that result into the string. Similar to what Jack posted:

Sub foo()
Dim a as long, b as long
a = 4
b = 5
' this is just text so what you see is exactly what you get
msgbox "a has the value: a and b has the value: b"

' but this evaluates the two variables and concatenates them into the text
msgbox "a has the value: " & a & "and b has the value: " & b
End Sub

Does that make sense? (I'm ignoring the second part of the question for the moment as it will likely just confuse the explanation for the first part!)

 

[billyheanue]

I'm getting the whole notion that things within quotes are treated as just literal text, and when you want to have code understand variables (when already within quotes), you need to " & (variable) & ".

Back to vlookup --
Jack's method works great, except the table array bit is tracking the correct region, but on the wrong page.

the result:
=VLOOKUP(50.588,$A$11:$I$44,9,FALSE)

but I still need the result to be
=VLOOKUP(50.588,'Price Data'!$A$11:$I$44,9,FALSE)

fVLOOKUP = "=VLOOKUP(@1,@2,@3,FALSE)"


fVLOOKUP = Replace(fVLOOKUP, "@1", Sheet9.Cells(rownum, colnum).Value)
fVLOOKUP = Replace(fVLOOKUP, "@2", Sheets("Price Data").Range("A12").CurrentRegion.Address)
fVLOOKUP = Replace(fVLOOKUP, "@3", colindexval)

should I replace @2 with Replace(fVLOOKUP, "@2", " 'Price Data'! " & " & Sheets("Price Data").Range("A12").CurrentRegion.Address)

^Something along those lines?