CODEWITHSUNDEEP
vbaexcel
Runeaway3
Runeaway3

Reputation: 1449

Using variable worksheet as formula destination in vba

I am writing a fairly complicated macro, but the problem I am having is creating a formula on one sheet, in the code "Input_Sheet", to equate itself to a cell in a newly created worksheet, variably set as "ws". Each iteration of ws is named, so that's not an issue. I figured the correct way to do it was: 

ActiveCell.FormulaR1C1 = " & ws.name & !R[" & totalRowCounter & "]C[" & totalColumnCounter & "]"

(Don't worry about the totalRowCounter & totalColumnCounter variables, they are defined appropriately). I just don't know why the formula isn't appropriately referencing the new ws sheet. Any thoughts?

Upvotes: 0

Views: 469

Answers (2)

Kyle
Kyle

Reputation: 2545

I think it would be the below:

ActiveCell.FormulaR1C1 = "=" & ws.name & "!R[" & totalRowCounter & "]C[" & totalColumnCounter & "]"

Upvotes: 1

Wyatt Shipman
Wyatt Shipman

Reputation: 1789

You just need to take your ws.name out of the quotes, also adding an apostrophe before and after the sheet name will help with any sheets that may have a space in the name:

ActiveCell.FormulaR1C1 = "='" & ws.name & "'!R[" & totalRowCounter & "]C[" & totalColumnCounter & "]"

Upvotes: 3

Related Questions