Formula/VBA Issue

PatrickW1907

New Member
Joined
Sep 3, 2022
Messages
18
Office Version
  1. 365
Platform
  1. 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 !

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
 

Attachments

  • Screenshot 2024-01-31 110700.png
    Screenshot 2024-01-31 110700.png
    27.9 KB · Views: 10

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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.
VBA Code:
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.
 
Upvote 0
Solution
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.
VBA Code:
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!!
 
Upvote 0
You are welcome.
Glad I was able to help!
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,246
Members
452,623
Latest member
cliftonhandyman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top