formula strings aggravation

[alabamatoy]

Excel 2013 macro code snippet:

ActiveCell.FormulaR1C1 = "=MID(D2,FIND(""."",D2,1)+1,FIND(""."",D2,FIND(""."",D2,1)+1)-FIND(""."",D2,1)-1)"

This formula within the quotes works (I'm using it in another excel file), but when the above code snippet sets the cell value, the actual resulting formula is:

=MID(D2,FIND(".",'D2',1)+1,FIND(".",'D2',FIND(".",'D2',1)+1)-FIND(".",'D2',1)-1)

which does NOT work. Why are there single quotes inserted around every cell reference, and how do I rectify this?

TIA....

 

[alabamatoy]

Correction, I left off one of the sets of single quotes:

=MID('D2',FIND(".",'D2',1)+1,FIND(".",'D2',FIND(".",'D2',1)+1)-FIND(".",'D2',1)-1)

 

[Nine Zero]

What cell is this being placed in?

Or actually yeah @Eric W is correct you could do it without the R1C1 mode

 

[Eric W]

Try:

ActiveCell.Formula =


(No R1C1)

 

[alabamatoy]

Try:

ActiveCell.Formula =


(No R1C1)

Your Excel-foo is strong, wise one. This fixed it, thanks much! Now, gently, help me understand why works this way!

 

[Nine Zero]

so R1C1 works with rows and columns from where you currently are so if you are in cell C5 and you want to reference something in cell D8 it would be 1 Column to the right and 3 Rows down so that would mean that it would be R[3]C[1]...i belive the brackets make it absolute reference or i could have the backwards not sure