Tip for creating syntax in VBA for Worksheet formulas
Anyone who has used VBA to create formulas in cells knows it can be a little tedious and tricky. Tricky because quotes need to be doubled and tedious because unlike a worksheet formula which gives you instant feedback, entering via VBA can mean running a few times to get it right.
But check out this gem of a tip from MarcinSzaleniec on the Stackoverflow site. https://stackoverflow.com/questions/49071815/convert-formula-in-vba from MarcinSzaleniec
So someone was asking why this code was returning an error…
With WS1.Range("AP2:AP" & LastRow) .Formula = "=IF(ISNUMBER(MATCH(B2,MTLS!B:B,0))=TRUE,"MTLS","NOT MTLS")" End With
His tip for incorporating a formula like this in VBA is basically get to work in the worksheet then…
Go to VBEditor, show Immediate window (Ctrl+G if you haven’t it visible already). Select cell with formula in the spreadsheet. Type in the Immediate:
and you get it. If you want to use it in the code, you need to double “s. Or you may type in immediate:
?Replace(cl.Formula, “”””, “”””””)
You will get formula with doubled quotation marks.
MarcinSzaleniec thank you that’s a great tip! The problem with the code by the way was the quotes needed to be doubled.
In the past I used the macro recorder to select the formula, pretend I am editing it then return it to it’s original state stop the recorder, this gave you the code. But this is quicker and better.