VBA excel R1C1 formula

0 votes
asked Mar 19, 2015 by user3549135

Why doesn't the VBA editor like this function? It runs fine within an excel cell but when I try to incorporate it into a Macro it is flagged as bad syntax.

ActiveCell.FormulaR1C1 = "=IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},LEFT(R2C1,1)))=0,MID(LEFT(R2C1,FIND(' ',R2C1,1)), FIND('.',R2C1,1)+1, FIND(' ',R2C1,1)), LEFT(R2C1,FIND(' ',R2C1,1)))"

Do I have to escape characters? Like empty quotes or Braces?

1 Answer

+1 vote
answered Mar 19, 2015 by rory

Your formula needs regular quotes (which you escape by doubling them) not single quotes:

ActiveCell.FormulaR1C1 = "=IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},LEFT(R2C1,1)))=0,MID(LEFT(R2C1,FIND("" "",R2C1,1)), FIND(""."",R2C1,1)+1, FIND("" "",R2C1,1)), LEFT(R2C1,FIND("" "",R2C1,1)))"
Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter