#NAME? Doesnt like the way i've referenced my VLOOKUP Range in VBA

[winneker]

I need to be able to place a number 1-28 in a cell and have my VLOOKUP reference that worksheet (1-28) and range A1:AK40 on all worksheets. Here is what i have and I get #NAME? which makes me feel like i'm pretty close. I dont yet have the Range A1:AK40 for the worksheets, but i figure i'll add that after i get the Name reference worked out.

The .Formula is working as expected (populating the proper cells and proper quantity of cells) it just populates with #NAME? instead of the values im looking for.

Sub awards()
Dim LastRow As Long
Dim j As Integer
Dim k As Integer
Dim ws1, ws2, ws3, ws4, ws5, ws6, ws7, ws8, ws9, ws10, ws11, ws12, ws13, ws14, ws15, ws16, ws17, ws18, ws19, ws20, _
ws21, ws22, ws23, ws24, ws25, ws26, ws27, ws28, ws29 As Worksheet

Set ws1 = ThisWorkbook.Sheets("Curve 1")
Set ws2 = ThisWorkbook.Sheets("Curve 2")
Set ws3 = ThisWorkbook.Sheets("Curve 3")
Set ws4 = ThisWorkbook.Sheets("Curve 4")
Set ws5 = ThisWorkbook.Sheets("Curve 5")
Set ws6 = ThisWorkbook.Sheets("Curve 6")
Set ws7 = ThisWorkbook.Sheets("Curve 7")
Set ws8 = ThisWorkbook.Sheets("Curve 8")
Set ws9 = ThisWorkbook.Sheets("Curve 9")
Set ws10 = ThisWorkbook.Sheets("Curve 10")
Set ws11 = ThisWorkbook.Sheets("Curve 11")
Set ws12 = ThisWorkbook.Sheets("Curve 12")
Set ws13 = ThisWorkbook.Sheets("Curve 13")
Set ws14 = ThisWorkbook.Sheets("Curve 14")
Set ws15 = ThisWorkbook.Sheets("Curve 15")
Set ws16 = ThisWorkbook.Sheets("Curve 16")
Set ws17 = ThisWorkbook.Sheets("Curve 17")
Set ws18 = ThisWorkbook.Sheets("Curve 18")
Set ws19 = ThisWorkbook.Sheets("Curve 19")
Set ws20 = ThisWorkbook.Sheets("Curve 20")
Set ws21 = ThisWorkbook.Sheets("Curve 21")
Set ws22 = ThisWorkbook.Sheets("Curve 22")
Set ws23 = ThisWorkbook.Sheets("Curve 23")
Set ws24 = ThisWorkbook.Sheets("Curve 24")
Set ws25 = ThisWorkbook.Sheets("Curve 25")
Set ws26 = ThisWorkbook.Sheets("Curve 26")
Set ws27 = ThisWorkbook.Sheets("Curve 27")
Set ws28 = ThisWorkbook.Sheets("Curve 28")
Set ws29 = ThisWorkbook.Sheets("Input Tab")

LastRow = Cells.Find("*", , , , xlByRows, xlPrevious).Row
Lastcolumn = Cells.Find("*", , , , xlByColumns, xlPrevious).Column

For k = 6 To LastRow 'sales loop
For j = 2 To (Range("J" & k) + 1) 'awards timephasing from 1 mo to POP Mo.

With Range("CX" & k).Offset(, (Range("I" & k) + (j - 2)))
.Formula = "=Range(""$Q"" & k) * VLOOKUP(Range(""$J"" & k),ws(Range(""$R"" & k)),j,FALSE)"
.Value = .Value
.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
End With

Next j
Next k

 

[bbott]

Your variables can't be in the quotes. You may have to adjust this, but it should look more like:

.Formula = "=($Q" & k & ") * VLOOKUP($J" & k & ")," & ws & "($R" & k & ")," & j & ",FALSE)"