Tip for creating syntax in VBA for Worksheet formulas

If you like this post please share it!
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

print
I monitor many Excel and VBA blogs using Feedly and see all sorts of tips, code and great solutions to collect and learn from along the way.  There is no better site for VBA than Stackoverflow.com

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:

?Selection.formula

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.

 
 
 
Tagged with: , , , ,