Formula/VBA Issue

[PatrickW1907]

Hi Guys,

Wondering if someone can help me please.

I've used the below for many formulas previously which has worked great in the past. I'm trying to do a IF formula looking at a column and finding cells that have a partial match to the EFX criteria I have mentioned. The formula works if I type this in the spreadsheet I just cannot get it to work in VBA due to a compile error, Expected End of Statement. It highlights the EFX part of my formula in blue (Literally the EFX characters, the speechmarks and asterix is not highlighted.

The idea is Column F will copy cells from Column C providing Cells contain EFX if they do not contain EFX it will grab the data in Column F instead of the relevant row. The idea being I am trying to remove 0's from a previous lookup. If someone could help get the code to run as per below that would be fab !

Sub Test67()
Worksheets("Data").Activate
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("F2:F" & LastRow).SpecialCells(xlBlanks).Formula = "=IF(COUNTIF(Data!$C2,"EFX*"),Data!$C2,Data!$F2)"
End Sub

 

[Joe4]

Because double-quotes are used as text indicators in VBA, and you are trying to use them as literal text in your formula, you need to double them up, i.e.

Range("F2:F" & LastRow).SpecialCells(xlBlanks).Formula = "=IF(COUNTIF(Data!$C2,""EFX*""),Data!$C2,Data!$F2)"

Note: One simple way to find out how you need to write the formula is to turn on your Macro Recorder and record yourself entering the formula on your sheet.
Then stop the recorder, and view the VBA code you just created to see how it needs to be written.

 

[PatrickW1907]

Because double-quotes are used as text indicators in VBA, and you are trying to use them as literal text in your formula, you need to double them up, i.e.

Range("F2:F" & LastRow).SpecialCells(xlBlanks).Formula = "=IF(COUNTIF(Data!$C2,""EFX*""),Data!$C2,Data!$F2)"

Note: One simple way to find out how you need to write the formula is to turn on your Macro Recorder and record yourself entering the formula on your sheet.
Then stop the recorder, and view the VBA code you just created to see how it needs to be written.

Brilliant thank you Joe!!

 

[Joe4]

You are welcome.
Glad I was able to help!