Solved: Use Worksheet Variable in vlookup code

[Djblois]

I am trying to use a worksheet variable in vlookup code. I have tried it with both the workbook variable and worksheet variable but I can't get it to work. Here is the code I am trying:

Range(Cells(2, 9), Cells(finalrow, 9)).FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-5],'wsPriceList'!C4:C11,8,FALSE)" & _
"+(VLOOKUP(RC[-5],'wsPriceList'!C4:C11,8,FALSE)*0.04)" & _
",VLOOKUP(RC[-5],'wsPriceList'!C4:C11,8,FALSE))"

or this:

Range(Cells(2, 9), Cells(finalrow, 9)).FormulaR1C1 = _
"=IFERROR(VLOOKUP(wsworkingsheet!RC[-5],'[" & wbRef.Name & "]wsPriceList'!C4:C11,8,FALSE)" & _
"+(VLOOKUP(wsworkingsheet!RC[-5],'[" & wbRef.Name & "]wsPriceList'!C4:C11,8,FALSE)*0.04)" & _
",VLOOKUP(wsworkingsheet!RC[-5],'[" & wbRef.Name & "]wsPriceList'!C4:C11,8,FALSE))"

[Bob Phillips]

Do you want the workbook name in the formula, the sheet name or both?

What is wsworkingsheet in this context?

[Djblois]

if it will work with just the sheet variable (albeit it is in a separate workbook than the one I am doing the formula in) then I am ok with just the worksheet variable. If it needs both then lets do it both.

wsWorkingsheet is the variable for the worksheet that the formula is going in and wbRef is the the variable for the workbook I am referencing and wsPriceList is the variable for the worksheet that I am referencing

[Bob Phillips]

You have a few things wrong here. You are mixing A1 and R1C1 notation in a R1C1 formul. That can be fixed, but you are also trying to lookup up the 8th column in a single column table. That just won't work.

[p45cal]

I think you're looking for the likes ofRange(Cells(2, 9), Cells(finalrow, 9)).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-5],'" & wsPriceList & "'!C4:C11,8,FALSE)+(VLOOKUP(RC[-5],'" & wsPriceList & "'!C4:C11,8,FALSE)*0.04),VLOOKUP(RC[-5],'" & wsPriceList & "'!C4:C11,8,FALSE))"
ps. I don't think you are mixing A1 and R1CI notation, it's just that C4:C11 looks like A1 notation but it's columns D:K.
I've left it as one long line so that the line continuation characters don't cloud the question.

and this perhaps for the second block:Range(Cells(2, 9), Cells(finalrow, 9)).FormulaR1C1 = "=IFERROR(VLOOKUP('" & wsworkingsheet & "'!RC[-5],'[" & wbRef.Name & "]" & wsPriceList & "'!C4:C11,8,FALSE)" & "+(VLOOKUP('" & wsworkingsheet & "'!RC[-5],'[" & wbRef.Name & "]" & wsPriceList & "'!C4:C11,8,FALSE)*0.04)" & ",VLOOKUP('" & wsworkingsheet & "'!RC[-5],'[" & wbRef.Name & "]" & wsPriceList & "'!C4:C11,8,FALSE))"
by the way, wouldn't
vlookup(a,b,c,d) + vlookup(a,b,c,d)*0.04
be replaceable with
vlookup(a,b,c,d)*1.04
just to keep it cleaner?

[Djblois]

p45Cal,

I keep getting an error when I run that line of code (both that you put up) The error says "Object doesn't support this property or method. And you are right about the *1.04, I just didn't think about it. Thank you.

[Djblois]

This is what I have now Cleaned up and I still get the error "Object Doesn't support this property or method"

Range(Cells(2, 9), Cells(finalrow, 9)).FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-5],'[" & wbRef.Name & "]" & wsPriceList & "'!C4:C11,8,FALSE)*1.04)" & _
",VLOOKUP(RC[-5],'[" & wbRef.Name & "]" & wsPriceList & "'!C4:C11,8,FALSE))"

I removed reference to wsWorking because right before it I have a line of code that activates it so it is redundant. I also simplified the code based on p45Cal suggestion. Please help me get this working.

[Djblois]

I fixed it. There were a couple of errors in that code. Here it is for anyone who comes across this thread:

Range(Cells(2, 9), Cells(finalrow, 9)).FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-5],'[" & wbRef.Name & "]" & wsPriceList.Name & "'!C4:C11,8,FALSE)*1.04" & _
",VLOOKUP(RC[-5],'[" & wbRef.Name & "]" & wsPriceList.Name & "'!C4:C11,8,FALSE))"