Leave a cell blank if found "Cancelled" in adjacent column

[jmklei0]

Please help.

I'm trying to leave a cell blank if the word "cancelled" shows up in the column to the right, but using a portion of the text in that adjacent column if the "Cancelled" does NOT show up.

I have a feeling that my quotes may be the problem, but I have little experience with VBA, so I don't know for sure.

Do you see why I keep getting syntax and/or object/application-defined errors?

Range("AE2").Select
ActiveCell.FormulaR1C1 = "=IFERROR(if(isnumber(search(""cancelled"",rc[1])),"" "",(LEFT(RC[1],SEARCH("" "",RC[1],1))),"" "")"


Thank you!

[Bernie Deitrick]

Try this:

Please Login or Register  to view this content.

The best way to work with formulas in VBA is to get your formula to work in a cell, then start recording a macro, select the cell, press F2 to get into Edit mode, then press Enter and stop recording the macro. You will get the correct syntax for the code based on that recorded code.

[jmklei0]

Excellent work, Sir! Thank you very much. It looks like the parentheses before "Left" was the issue. I was trying to copy a portion of a formula from earlier and didn't get the cut-off right.

Thanks!