VBA - Reference Workbook in R1C1 Formula

[spencerp237]

Set Sourcewb = ActiveWorkbook
Set Destwb = Workbooks.Add

With Destwb.Sheets("Pay")
    .Range("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("B3").FormulaR1C1 = "=VLOOKUP(RC[-1],Sourcewb.Sheets(""Strategy"").Range(C5:C52),48,FALSE)"
    .AutoFill Destination:=Range("B2:B" & payLR)
End With

I am trying to reference my Sourcewb in VLOOKUP in an R1C1 formula. I know the code above is wrong, but it's just there to highlight what I'm trying to do. The "Sourcewb.Sheets(""Strategy"").Range(C5:C52)" part is what I am not sure how to do. Thanks for any help!

Edit:
Just clarifying that I want that part to reference Column E:AZ on the Strategy worksheet

 

[Roxxien]

My question is how would you write your formula normally in your B3 cell in Excel?

By knowing that, why don't you use the macrorecorder to know the exact syntax on how to do this.

However, I can still give you that information.

.Range("B3").FormulaR1C1 = "=VLOOKUP(RC[-1],[YYY.xlsx]Strategy!R5C3:R52C3,48,False)"

As I don't know your activeworkbook reference, I can't put he real workbook name so I wrote YYY.

 

[spencerp237]

Hi Roxxien,

Thank you! I did use the macrorecorder; however, I am wondering if there is a way to assign the workbook name to a variable and use that in the formula.

So for example, if the real workbook name is YYY, is there a way to assign that to a variable, like Sourcewb, and use that instead? The reason being is that as I make new versions of this spreadsheet, I need to change the name, and want to make it so that I don't need to go into the code each time and change this reference. So the work book I'm working on now is YYY13. There is YYY1 through YYY12 that I have used in the past.

 

[Roxxien]

In the end, you are creating a string to put inside your .FormulaR1C1

So yeah, you can do something like this

.FormulaR1C1 = "=IF(" & variable1 & "<3,1,2)"

Just like creating a string, you have no limit on the way you create your formula to write in an Excel cell.

By retaking what I wrote before, you can have the following.

.Range("B3").FormulaR1C1 = "=VLOOKUP(RC[-1],[" & SourceWB & "]Strategy!R5C3:R52C3,48,False)"

However, you will need to verify if the variable save the extension (.xlsx or .xlsm) as I don't remember.

 

[spencerp237]

That doesn't seem to work either.

.Range("B3").FormulaR1C1 = "=VLOOKUP(RC[-1],[" & Sourcewb & "]Database_Strategy!C5:C52,48,FALSE)"
.Range("B3").FormulaR1C1 = "=VLOOKUP(RC[-1],["" & Sourcewb & ""]Database_Strategy!C5:C52,48,FALSE)"
.Range("B3").FormulaR1C1 = "=VLOOKUP(RC[-1],'[" & Sourcewb & "]Database_Strategy'!C5:C52,48,FALSE)"
.Range("B3").FormulaR1C1 = "=VLOOKUP(RC[-1],'["" & Sourcewb & ""]Database_Strategy'!C5:C52,48,FALSE)"

I tried all of these. The only one that even ran was the bottom line, and gives a result of =VLOOKUP(A3,'[" & Sourcewb & "]Database_Strategy'!$E:$AZ,48,FALSE) in the cell.

 

[Roxxien]

Did you verify what is the value saved in your variable?

As well, did it work without a variable and directly writing the workbook name?