I'm trying to do this in macro:
A cell should be filled with =CONCATENATE("[Text]",Variable,"[Text2]")
Variable is a number in a different case a string.
Let's say: Text=Hello, Variable is filled with 1, Text2=Community
Then it should say in the cell
=CONCATENATE("Hello";"1";"Community")
and it should be read as: Hello1Community
When I try ActiveCell.FormulaR1C1 = "=CONCATENATE(""Hello"",Variable,""Community"")" then I get this:
=CONCATENATE("Hello";Variable;"Community").
Sadly it is the name of the Variable but not its content. I get the #Name? error because the Variable is not wrapped around "".
In a different case I'm trying to put a reference to a previous sheet and cell.
In my macro I'm creating a new sheet and want to reference to it. I save the sheet's name into a variable (string). Then create a new sheet and certain cells should reference to the previous sheet.
Let's say previous sheet's name = sheet1 Cell I wanna reference to = B4
Then I want to fill the new cell with: =sheet1!B4
sheet1 is in a variable.
When I try ActiveCell.FormulaR1C1 = "=" & variable & "!B4", then it fills the cell with =sheet1'!B4'
I get the #Name? error because of the ' '
