使用VBA将公式写入单元格时出现语法错误

Question

不太熟悉visual basic，只是参考了一些资料。

我想问一下，既然我花了一个小时来解决这个问题，也许我需要一些建议。我在Visual Basic中遇到这个错误，为excel制作宏。起初它是工作的，但在我添加了一些行之后，语法变成了红色，当运行宏时，它只有一个

Compile Error: Syntax Error

Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNUMBER(SEARCH(""California"",RC[-2],1)),""California"", 
IF(ISNUMBER(SEARCH(""Florida"",RC[-2],1)),""Florida"", 
IF(ISNUMBER(SEARCH(""Texas"",RC[-2],1)),""Texas"", IF(ISNUMBER(SEARCH(""New 
Mexico"",RC[-2],1)),""New Mexico"", IF(ISNUMBER(SEARCH(""Alaska"",RC[-2],1)),""Alaska"", 
IF(ISNUMBER(SEARCH(""New Jersey"",RC[-2],1)),""New Jersey"",
""""))))))&IF(ISNUMBER(SEARCH(""Marikina"",RC[-2],1)),""Marikina"", 
IF(ISNUMBER(SEARCH(""Maryland"",RC[-2],1)),""Maryland"", 
IF(ISNUMBER(SEARCH(""Nebraska"",RC[-2],1)),""Nebraska"", 
IF(ISNUMBER(SEARCH(""Pennsylvania"",RC[-2],1)),""Pennsylvania"",   
IF(ISNUMBER(SEARCH(""Illinois"",RC[-2],1)),""Illinois"",
IF(ISNUMBER(SEARCH(""Colorado"",RC[-2],1)),""Colorado"",""""))))))&
IF(ISNUMBER(SEARCH(""Louisiana"",RC[-2],1)),""Louisiana"", 
IF(ISNUMBER(SEARCH(""Idaho"",RC[-2],1)),""Idaho"", 
IF(ISNUMBER(SEARCH(""Hawaii"",RC[-2],1)),""Hawaii"", 
IF(ISNUMBER(SEARCH(""Vermont"",RC[-2],1)),""Vermont"", 
IF(ISNUMBER(SEARCH(""West Virginia"",RC[-2],1)),""West Virginia"",           
IF(ISNUMBER(SEARCH(""Connecticut"",RC[-2],1)), 
""Connecticut"","""")))))"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D38"), Type:=xlFillDefault
Range("D2:D38").Select
ActiveWindow.SmallScroll Down:=-39
End Sub

Answer 1

您不能只是添加换行符，您需要将公式拆分为多个字符串并将它们与&连接起来。换行需要与行尾的_连接。

Range("D2").FormulaR1C1 = _
"=" & _
"IF(ISNUMBER(SEARCH(""California"",RC[-2],1)),""California"", " & _
"IF(ISNUMBER(SEARCH(""Florida"",RC[-2],1)),""Florida""," & _
"IF(ISNUMBER(SEARCH(""Texas"",RC[-2],1)),""Texas""," & _
"IF(ISNUMBER(SEARCH(""New Mexico"",RC[-2],1)),""New Mexico""," & _
"IF(ISNUMBER(SEARCH(""Alaska"",RC[-2],1)),""Alaska""," & _
"IF(ISNUMBER(SEARCH(""New Jersey"",RC[-2],1)),""New Jersey""," & _
"""""))))))&" & _
"IF(ISNUMBER(SEARCH(""Marikina"",RC[-2],1)),""Marikina""," & _
"IF(ISNUMBER(SEARCH(""Maryland"",RC[-2],1)),""Maryland""," & _
"IF(ISNUMBER(SEARCH(""Nebraska"",RC[-2],1)),""Nebraska""," & _
"IF(ISNUMBER(SEARCH(""Pennsylvania"",RC[-2],1)),""Pennsylvania""," & _
"IF(ISNUMBER(SEARCH(""Illinois"",RC[-2],1)),""Illinois""," & _
"IF(ISNUMBER(SEARCH(""Colorado"",RC[-2],1)),""Colorado""," & _
"""""))))))&" & _
"IF(ISNUMBER(SEARCH(""Louisiana"",RC[-2],1)),""Louisiana""," & _
"IF(ISNUMBER(SEARCH(""Idaho"",RC[-2],1)),""Idaho""," & _
"IF(ISNUMBER(SEARCH(""Hawaii"",RC[-2],1)),""Hawaii""," & _
"IF(ISNUMBER(SEARCH(""Vermont"",RC[-2],1)),""Vermont""," & _
"IF(ISNUMBER(SEARCH(""West Virginia"",RC[-2],1)),""West Virginia""," & _
"IF(ISNUMBER(SEARCH(""Connecticut"",RC[-2],1)),""Connecticut""," & _
"""""))))))"
Range("D2").AutoFill Destination:=Range("D2:D38"), Type:=xlFillDefault

你可能从阅读中受益如何避免在Excel VBA中使用选择。

---

为了减少过程的重复，创建一个可以使用状态列表生成公式的函数，例如:

Option Explicit
Public Sub Example()

Dim ListOfStates() As Variant
ListOfStates = Array("California", "Florida", "Texas", "New Mexico", "Alaska", "New Jersey", "Marikina", "Maryland", "Nebraska", "Pennsylvania", "Illinois", "Colorado", "Louisiana", "Idaho", "Hawaii", "Vermont", "West Virginia", "Connecticut")


Range("D2").FormulaR1C1 = "=" & Join(CreateFormulaBlocks(ListOfStates), "&") 'join all blocks by &
End Sub
' returns an array of all blocks (each 6 states)
'
Public Function CreateFormulaBlocks(ByVal ListOfStates As Variant, Optional ByVal MaxPerBlock As Long = 6) As Variant
' how many blocks do we need
Dim MaxBlocks As Long
MaxBlocks = (UBound(ListOfStates) + 1)  6  ' note this is no normal division / but an integer division 

' create array for blocks
Dim ReturnBlocks() As Variant
ReDim ReturnBlocks(MaxBlocks - 1) As Variant

' create blocks
Dim iBlock As Long
For iBlock = 0 To MaxBlocks - 1
Dim BlockStates() As Variant
ReDim BlockStates(MaxPerBlock - 1) As Variant
Dim iState As Long
For iState = 0 To MaxPerBlock - 1
BlockStates(iState) = ListOfStates(iBlock * 6 + iState)
Next iState

' create one block of 6 states
ReturnBlocks(iBlock) = CreateFromulaBlock(BlockStates)
Next iBlock

' return all blocks as array
CreateFormulaBlocks = ReturnBlocks
End Function

' returns one block of 6 states
' eg IF(ISNUMBER(SEARCH("California",RC[-2],1)),"California", IF(ISNUMBER(SEARCH("Florida",RC[-2],1)),"Florida", IF(ISNUMBER(SEARCH("Texas",RC[-2],1)),"Texas", IF(ISNUMBER(SEARCH("New Mexico",RC[-2],1)),"New Mexico", IF(ISNUMBER(SEARCH("Alaska",RC[-2],1)),"Alaska", IF(ISNUMBER(SEARCH("New Jersey",RC[-2],1)),"New Jersey", ""))))))
'
Public Function CreateFromulaBlock(ByVal States As Variant) As String
Dim FormulaString As String

Dim State As Variant
For Each State In States
FormulaString = FormulaString & "IF(ISNUMBER(SEARCH(""" & State & """,RC[-2],1)),""" & State & """, "
Next State

CreateFromulaBlock = FormulaString & """""" & String(UBound(States) + 1, ")")
End Function