Continuation of Excel Formula

[zalik22]

Hi, I am trying to enter the following code so the following formula copies in column E but keep getting an error. The formula is about 3 times longer, I shortened it for the post.

With Sheets("data")
.Range("E9:E" & .Cells(.Rows.Count, "A").End(xlUp).Row).Formula ="
IF(ISERROR(SEARCH("*aaa*",f9,1))=FALSE,"aaa",
IF(ISERROR(SEARCH("*bbb*",f9,1))=FALSE,"bbb",
IF(ISERROR(SEARCH("*ccc*",f9,1))=FALSE,"ccc",
IF(ISERROR(SEARCH("*ddd*",f9,1))=FALSE,"ddd","error"))))"

Thanks!

 

[Joe4]

The easiest way to do this is to turn on the Macro Recorder, and record yourself entering the formula in cell F9 (or if the formula is already there, turn on the Macro Recorder, go to that cell, press F2 to edit the formula, and then press enter).

This will give you the formula in relative reference format (.FormulaR1C1), which is the best way of doing it when applying the formula to a multi-cell range.
So, your code would look something like:

[COLOR=#333333]With Sheets("data")[/COLOR]
[COLOR=#333333]    .Range("E9:E" & .Cells(.Rows.Count, "A").End(xlUp).Row).[/COLOR][COLOR=#ff0000]FormulaR1C1[/COLOR][COLOR=#333333] = "[I]your recorded formula here[/I]"
[/COLOR]End With

 

[pbornemeier]

Try

    With Sheets("data")
        With .Range("E9:E" & .Cells(.Rows.Count, "A").End(xlUp).Row)
            .FormulaR1C1 = _
                "= IF(ISERROR(SEARCH(""*aaa*"",RC[1],1))=FALSE,""aaa"", " & _
                " IF(ISERROR(SEARCH(""*bbb*"",RC[1],1))=FALSE,""bbb"", " & _
                " IF(ISERROR(SEARCH(""*ccc*"",RC[1],1))=FALSE,""ccc"", " & _
                " IF(ISERROR(SEARCH(""*ddd*"",RC[1],1))=FALSE,""ddd"",""error""))))"
            Application.Calculate
            Do While Application.CalculationState <> xlDone: DoEvents: Loop
            .Value = .Value
        End With
    End With

The easiest way I have found to get a cell formula into VBA is to get it working in the cell then use the macro recorder to translate it into VBA. It may take a bit of editing, but it is easier than composing in in the VBE.

 

[zalik22]

It works, but for some reason E9 to E15 is blank, even though there is data in Column A. Any ideas?

 

[zalik22]

Fixed it, I had an incorrect reference.

 

[pbornemeier]

Stop the code so the formulae are still live and examine each portion of the formula clicking on parts of the formula in the formula bar and then clicking fx (at the left end of the formula bar) to see what the part evaluates to.