0

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?

Improve this question
0

2 Answers 2

Reset to default
3

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 & "]"
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks @ScottCraner, I was updating that and adding the apostrophe info
Thank you very much Wyatt. Can you explain, briefly, what exactly the & do? I recognize that I have had to use them when passing a variable as an argument... but I don't really get why.
They are used to concatenate various strings together, so you are concatenating =' with the sheet name and so on. In an excel formula, it would be equivalent to =CONCATENATE(string1,string2) or =string1&string2
1

I think it would be the below:

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

1 Comment

I thinks it's worth quite a lot as the answer is wrong otherwise ;)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.