Change workbook reference to variable

[Djblois]

I am trying to change this part [BusinessReportingReference.xlsm] in the following code to the variable that I use wbRef


rngAddto.FormulaR1C1 = _
"=VLOOKUP(RC[" & intColumnDiff & "],'[BusinessReportingReference.xlsm]Products'!C1:C2,2,FALSE)"

I can't figure it out

[figment]

sould be something like this


rngAddto.FormulaR1C1 = _
"=VLOOKUP(RC[" & intColumnDiff & "],'[" & wbRef.Name & "]Products'!C1:C2,2,FALSE)"

[Djblois]

I am in the process of testing this code and with both my old version of code and the new version I am getting the same error application-defined or object-defined error. I never recieved this error for this code under Excel 2003 but under 2007 I am. Here is the full code:

Old:

Sub comAddCust(ByVal rngAddto As Range, ByVal intColumnDiff As Integer)

rngAddto.FormulaR1C1 = _
"=VLOOKUP(RC[" & intColumnDiff & "],'[BusinessReportingReference.xlsx]Cust'!C1:C2,2,FALSE)"
rngAddto.Value = rngAddto.Value
End Sub

New:

Sub comAddCust(ByVal rngAddto As Range, ByVal intColumnDiff As Integer)

rngAddto.FormulaR1C1 = _
"=VLOOKUP(RC[" & intColumnDiff & "],'[" & wbRef.Name & "]Cust'!C1:C2,2,FALSE)"
rngAddto.Value = rngAddto.Value
End Sub

[Djblois]

For anybody that has this problem I fixed it. The problem is the worksheet that I was performing the vlookup on was the old format and the lookup sheet was the new format. This caused it to crash.

Thank you all for your help.