Formula If And statement via VBA

• 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]

• 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"",""""))"``````

Rory
Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

• 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``````

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!