Excel/VBA: Variable Value/Text into concatenate formula

Question

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 ' '

Solution

In the first case, you need to work out where to put double quotes:

    ActiveCell.FormulaR1C1 = "=CONCATENATE(""Hello"",""" & Variable & """,""Community"")"

In the second case, you need to work out where to put single quotes, and change the property to Formula instead of FormulaR1C1:

    ActiveCell.Formula = "='" & Variable & "'!B4"

Comments

[padawan_IT]

You sir are awesome! Worked like a charm!