Excel/VBA：将变量值/文本转换为连接公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45730147/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel/VBA: Variable Value/Text into concatenate formula
提问by padawan_IT
I'm trying to do this in macro:
我试图在宏中做到这一点：
A cell should be filled with =CONCATENATE("[Text]",Variable,"[Text2]")
一个单元格应该填充 =CONCATENATE("[Text]",Variable,"[Text2]")
Variable is a number in a different case a string.
变量是数字，另一种情况是字符串。
Let's say: Text=Hello, Variableis filled with 1, Text2=Community
假设： Text=Hello，变量填充为1，Text2=Community
Then it should say in the cell
然后它应该在单元格中说
=CONCATENATE("Hello";"1";"Community")
and it should be read as: Hello1Community
它应该读作：Hello1Community
When I try ActiveCell.FormulaR1C1 = "=CONCATENATE(""Hello"",Variable,""Community"")"then I get this:
=CONCATENATE("Hello";Variable;"Community").
当我尝试ActiveCell.FormulaR1C1 = "=CONCATENATE(""Hello"",Variable,""Community"")"然后我得到这样的：
=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 "".
可悲的是，它是变量的名称，而不是其内容。我得到了#Name？错误，因为变量没有环绕“”。
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
假设上一张工作表的名称 = sheet1 我要引用的单元格 = B4
Then I want to fill the new cell with: =sheet1!B4
然后我想用以下内容填充新单元格： =sheet1!B4
sheet1 is in a variable.
sheet1 在一个变量中。
When I try ActiveCell.FormulaR1C1 = "=" & variable & "!B4", then it fills the cell with =sheet1'!B4'
当我尝试时ActiveCell.FormulaR1C1 = "=" & variable & "!B4"，它会用=sheet1'!B4'
I get the #Name?error because of the ' '
我得到了#Name？由于 ' ' 导致的错误
回答by Olly
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 Formulainstead of FormulaR1C1:
在第二种情况下，您需要确定放置单引号的位置，并将属性更改Formula为FormulaR1C1：
ActiveCell.Formula = "='" & Variable & "'!B4"
