Enter formula in cell

[Exact]

Hoping you can help me out with something that I think is going to be pretty simple.

I'm using a Mac with 365

If I enter the following directly into in an Excel cell it works fine.
=XLOOKUP(IFERROR(INDEX($Q$2:$Q$210,MATCH(1,COUNTIF(D2,"*"&$Q$2:$Q$210&"*"),0)),""),$Q$2:$Q$210,$R$2:$R$210,"")

Using VBA I've been trying to enter the formula into an Excel cell but I can't get it to work.
For the wildcard values VBA kept adding spaces either side so I added " quotes which seem like they have solved the space problem e.g. " * " needs ""*"" to get rid of the extra space vba is putting in.

However I still can't enter the formula into a cell e.g.

Range("F2").formula ="= etc. fails with method formulation of object 'Range' failed
Range("F2").select
ActiveCell.formula ="= etc. fails with an error.

I don't know why VBA is compiling the code when all I want to do is enter the formula into an Excel cell.
I'd appreciate your help. Thanks

 

[Burrgogi]

Welcome to the forum.

Your error is that you're trying to use 2 equal signs to enter the formula. Unless there is something special about how Excel VBA works on Mac systems, that's the wrong method.

Maybe try:

Range("F2").Formula = "=XLOOKUP(IFERROR(INDEX($Q$2:$Q$210,MATCH(1,COUNTIF(D2,"*"&$Q$2:$Q$210&"*"),0)),""),$Q$2:$Q$210,$R$2:$R$210,"")"

 

[StephenCrump]

You need to double up any quote marks inside the formula string:

Range("F2").Formula2 = "=XLOOKUP(IFERROR(INDEX($Q$2:$Q$210,MATCH(1,COUNTIF(D2,""*""&$Q$2:$Q$210&""*""),0)),""""),$Q$2:$Q$210,$R$2:$R$210,"""")"

 

[Exact]

You need to double up any quote marks inside the formula string:

Range("F2").Formula2 = "=XLOOKUP(IFERROR(INDEX($Q$2:$Q$210,MATCH(1,COUNTIF(D2,""*""&$Q$2:$Q$210&""*""),0)),""""),$Q$2:$Q$210,$R$2:$R$210,"""")"

Thank for this, it now works and I also needed to use the "Formula2" as it doesn't work with "Formula" so thanks for that as well !!
From what I've just been reading Formula2 has replaced Formula so I should always use Formula2 is that right?

 

[StephenCrump]

From what I've just been reading Formula2 has replaced Formula so I should always use Formula2 is that right?

The quick answer is that if you're always working in Excel 365, you're probably OK always using .Formula2

The longer answer is more nuanced - .Formula and .Formula2 both exist, and depending on the formula being applied, may work differently.