PatrickW1907
New Member
- Joined
- Sep 3, 2022
- Messages
- 18
- Office Version
- 365
- Platform
- Windows
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 !
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 !
VBA Code:
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