CODEWITHSUNDEEP
excelvba
Issac_n
Issac_n

Reputation: 89

Multiple quotation marks in-vba formula

My first coding is working properly and return correct answer.

ActiveSheet.Range("I2:I" & LastROwColumnList).Formula = "=IF(MID(F2,8,3)=""T_2"",""Team_A"",""NA2"")"

but it doesnt work when trying to put more conditions in formula

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"")"

How should I modify it?

Upvotes: 0

Views: 125

Answers (2)

J. Stott
J. Stott

Reputation: 304

You're missing a closing parenthesis for the outer IF function since you added an inner IF function.

This should do it:

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""))"

Upvotes: 1

iDevlop
iDevlop

Reputation: 25272

Enter the formula manually in the sheet, then go to the debug window and type 

print activecell.formula

You can then double the inner quotes, or replace them by single quotes, and your're done.
You can even automate that step (I wrote that in 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

Upvotes: 1

Related Questions