Challenge - Excel Challenge #903 (How to use double quotes in a formula in a VBA code)

To insert a formula in a range you can use the range.formula command in VBA. Like for example you want to add the columnA +columnB values and put in column C. Lets say we want to put this formula in C2 then we write it as below

Range("c2").formula="=A2+B2"

This is fine if it’s a small formula but the issue comes when we have double quotes in a formula. Imagine a scenario like the below. Here we have vendor names in column B and based on the 1st character of vendor we need to put vendor code. So anything begins with A the code is 1, anything that begins with B the code is 2 and the remaining are 3.

We can achieve this with a formula in cell a2

=IF(LEFT(B2,1)="A",1,IF(LEFT(B2,1)="B",2,3))

To have the same thing in VBA we can use the code range("b2").formula="……"

We replace the dots …. With the actual formula but it wont work due to double quotes so what needs to be done is a very simple thing. Take the formula in a word file or a note pad and replace one double quote with 2 double quotes. So after doubling the double quotes the formula in VBA code would look something like the below.

Range("a2").Formula = "=IF(LEFT(B2,1)=""A"",1,IF(LEFT(B2,1)=""B"",2,3))"

Try to do the same with VBA! Best of luck.