1

I have a situation here. There is a excel sheet in which lets say cell A1 as following formula.

=(IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(B10,-1,0,1,1),".",""))),1,IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(B10,-1,0,1,1),".","`",1))),VALUE(OFFSET(B10,-1,0,1,1))+1,VALUE(LEFT(OFFSET(B10,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(B10,-1,0,1,1),".","`",1))-1))+1)))

I am writing a sub to put this same formula in the active /selected cell however while doing so, VBA encounter error since while reading "." in the formula , it expect

Screenshot of error

The code is reproduced below. I am simply trying to put a formula which otherwise is used directly in excel but this time via a button which will put this formula in an active cell. How can I do that?

Sub fillmainwbs()
    ActiveCell.Formula = "=(IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(B10,-1,0,1,1),".",""))),1,IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(B10,-1,0,1,1),".","`",1))),VALUE(OFFSET(B10,-1,0,1,1))+1,VALUE(LEFT(OFFSET(B10,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(B10,-1,0,1,1),".","`",1))-1))+1)))"
End Sub
waka
  • 3,362
  • 9
  • 35
  • 54
Ravi Kant Agarwal
  • 35
  • 1
  • 8

1 Answers1

2

In VBA, formulas are specified as a string. The first double quote encountered in your formula at "." is considered the end of the formula string and then the rest of the line is syntactically incorrectly.

To specify a double quote character within a string in VBA (which is what you need to do when your Excel formula includes a double quote), you need to use a double-double quote: ""

When VBA sees "" within a string, it will treat it as the character " and not the end of the string containing the formula.

For all double quotes in your formula, you need to replace them with double-double quotes:

"=(IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(B10,-1,0,1,1),""."",""""))),1,IF(ISERROR(FIND(""`"",SUBSTITUTE(OFFSET(B10,-1,0,1,1),""."",""`"",1))),VALUE(OFFSET(B10,-1,0,1,1))+1,VALUE(LEFT(OFFSET(B10,-1,0,1,1),FIND(""`"",SUBSTITUTE(OFFSET(B10,-1,0,1,1),""."",""`"",1))-1))+1)))"

Note that the null string "" used in an Excel formula actually needs to be represented within a string as:

""""

Basically, after a string is opened in VBA, the first double quote encountered after the opening double quote represents the end of the string. All double-double quotes represent a double quote character in the string.

So, if you needed to specify this formula in VBA:

A1 = ""

It would be specified as this string:

"A1 = """""

The first double quote opens the string. The 2nd and 3rd double quotes represent the first double quote character in the formula The 4th and 5th double quotes represent the second double quote character in the formula The 6th double quote closes the string.

Michael
  • 4,563
  • 2
  • 11
  • 25