Formula If And statement via VBA

Joe Derr · Mar 7th 2011

Hi All... I need help with this formula..

What it's supposed to do is IF (on that row Column E) = PASS Then that cell = "NA" and also IF (on that row Column E) = NONRATED --- AND --- (on that row Column G) = TRN then the cell will have a NA otherwise leave it blank.

Column E Column G Column J and K
---------- ---------- ---------------
PASS numerous options NA
NONRATED TRN NA
NONRATED TDV (blank cell out)

Code

Dim TRow As String
    Do While Not IsEmpty(ActiveCell.Offset(0, -1))
    TRow = ActiveCell.Row
    Formula = "=IF(E" & TRow & " =""PASS"",""NA"","""") & (E" & TRow & "G" & TRow & "=""TRN"",""NA"","""")"
 
ActiveCell.Formula = Formula
    
    
    ActiveCell.Offset(1, 0).Select
    Loop

My formula code fails miserably... before I needed to add another criteria to the formula it was working fine... that code is below...

Code

[/FONT][FONT=Tahoma]    Formula = "=IF(E" & TRow & " =""PASS"",""NA"","""")"[/FONT][FONT=Tahoma]

[/FONT]

**rory** · Mar 7th 2011

Re: Formula If And statement via VBA

It's unnecessary to select cells generally. Something like:

Code

Dim lngLastRow as long
lnglastrow = cells(rows.count, activecell.column - 1).end(xlup).Row
Range(activecell, cells(lnglastrow, activecell.column)).formular1c1 = "=IF(RC5=""PASS"",""NA"",IF(AND(RC5=""NONRATED"",RC7=""TRN""),""NA"",""""))"

Joe Derr · Mar 7th 2011

Re: Formula If And statement via VBA

Thanks Rory!

I added your code to the one I had, I will look at your whole solution off line.. I slid this on the active workbook for a quick fix.. Now to tackle the conditional formatting ..

Thanks again..

Code

Dim TRow As String
    Do While Not IsEmpty(ActiveCell.Offset(0, -1))
    TRow = ActiveCell.Row
    Formula = "=IF(E" & TRow & " = ""PASS"",""NA"",IF(AND(E" & TRow & "=""NONRATED"",G" & TRow & "=""TRN""),""NA"",""""))"
    ActiveCell.Formula = Formula
    
    
    ActiveCell.Offset(1, 0).Select
    Loop

Formula If And statement via VBA

Participate now!

Share