我的第一个编码工作正常,并返回正确答案。
ActiveSheet.Range("I2:I" & LastROwColumnList).Formula = "=IF(MID(F2,8,3)=""T_2"",""Team_A"",""NA2"")"
但当试图在公式中设置更多条件时,它不起作用
ActiveSheet.Range("I2:I" & LastROwColumnList).Formula = "=IF(MID(F2,8,3)=""T_2"",""Team_A"",IF(MID(F2,8,3)=""P_9"",""Team_B"",""NA"")"
我应该如何修改它?
在工作表中手动输入公式,然后转到调试窗口并键入
print activecell.formula
然后,您可以将内引号加倍,或者用单引号替换,您就完成了
你甚至可以自动化这一步(我在2009年写过(:
Sub RngToVba(src As Range)
'writes the VBA code to re-create the formulae in given range
'by Patrick Honorez - www.idevlop.com
'usage: from debug window, type RngToVba [L14:R14]
' or RngToVba range("L14:R14")
Dim c As Range
For Each c In src
Debug.Print "range(""" & c.Address & """).formula = """ & _
Replace(c.Formula, """", """""") & """"""
Next c
End Sub
由于添加了内部IF函数,因此外部IF函数缺少一个右括号。
这应该做到:
ActiveSheet.Range("I2:I" & LastROwColumnList).Formula = "=IF(MID(F2,8,3)=""T_2"",""Team_A"",IF(MID(F2,8,3)=""P_9"",""Team_B"",""NA""))"