Nested If statement for VBA, excel formula to vba code translation help.

[Huby00]

Hello,

I am trying to figure out a way to return one of two results if a condition is met.

ActiveCell.FormulaR1C1 = "=IF(ISERR(FIND(""R"",RC[-1])),""Virgin"",""Rewash"")"

The code above worked fine, but I needed to include another condition. I tried the following code:

ActiveCell.ForumulaR1C1 = "=IF(ISERR(FIND(""R"",RC[-1])),IF(ISERR(FIND(""AW"",RC[-1])),""Virgin"",""Rewash""),""Rewash"")"

For some reason unknown to me, it does not work. When I did a nested if statement in excel (tried to check if it was even permitted in excel) it worked fine.

=IF(ISERR(FIND("R",I67)),IF(ISERR(FIND("AW",I67)),"Virgin","Rewash"),"Rewash")[B][/[/B]CODE]
 
Can someone enlighten me on the proper excel to vba translation? Or is it just one of those things that you can't do?
 
The entire code if it helps:
[CODE]Sub initformatting3()
'
'
    LastRow = Range("A65536").End(xlUp).Row
    
    Columns("B:B").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("J:AE").Select
    Selection.Delete Shift:=xlToLeft
    Columns("L:P").Select
    Selection.Delete Shift:=xlToLeft
    Columns("N:AB").Select
    Selection.Delete Shift:=xlToLeft
    Columns("I:I").Select
    Selection.Replace What:="r", Replacement:="R", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="w", Replacement:="W", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="b", Replacement:="B", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="p", Replacement:="P", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("J:J").Select
    Selection.Insert Shift:=xlToRight
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "RW"
    ActiveWindow.WindowState = xlNormal
    ActiveWindow.WindowState = xlNormal
    Range("J2").Select
    ActiveCell.ForumulaR1C1 = "=IF(ISERR(FIND(""R"",RC[-1])),IF(ISERR(FIND(""AW"",RC[-1])),""Virgin"",""Rewash""),""Rewash"")"
    
    'ActiveCell.FormulaR1C1 = "=IF(ISERR(FIND(""R"",RC[-1])),""Virgin"",""Rewash"")"
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J" & LastRow)
    Range("J2:J" & LastRow).Select
  
End Sub

 

[Fazza]

'instead of
ActiveCell.ForumulaR1C1
 
'use
ActiveCell.FormulaR1C1

 

[Huby00]

wow. Thanks.

I can't believe that I missed that. Thanks.