autofill with VBA formula that contains quotes

Below is the formula I have to grab an inconsistently formatted "loan number" to formatted text so I can use the second digit as criteria:
IOW Loan # in B2 is 1136578911, second digit is 1, use it as criteria in IF statement
So when I use this formula in excel, works beautifully. However, I want to hardcode it in my vba so it can autofill it. My VBA does not like the quotes and gives me the Compile error: Expected end of statement with each set of quotes. I tried double quotes in vba, ie. TEXT(B2, "0000000") has to be TEXT(B2, ""0000000""), but that formula is unrecognizable in the worksheet. How to I write this so the exact formula will be pasted in via VBA. I posted the entire VBA. I "think" I put the trouble formula in "red". If I screwed it up, it is 3/4ths of way down and only formula in the code. Thank you.

Please Login or Register  to view this content.

Hi,
You found the first problem in the highlighted line (the lack of escaped quotes), but missed the second. .FormulaR1C1 means that the formula is entered in R1C1 style notation, as opposed to the typical A1 style notation. This is what is causing the #Name? error: Excel doesn't understand what cells you're refering to. Instead of using .FormulaR1C1, just use .Formula (and, of course, escape the double quotes)

Please Login or Register  to view this content.

To see what's happening in a hands on manner, look in Excel's Options--> Formulas and select the R1C1 check box, and then try entering a simple formula somewhere like =A1+1, and then try using a formula where you actually click on input range, and you'll see a formula (if entered in "c1") of =RC[-2]+1

Good morning, (my morning, I guess) CJO. Thank you very much for your help, as I am not an "excel gal" so was totally unaware of .formula vs R1C1. So I changed that, but have been unable to test my vba because my debugger still gives me the compile error: expected end of statement on the 7 zeros. I know it has to do with the quotes, but I am lost. That was why I originally tried the double quotes. Here is my latest version of the code line. I want to be sure the ActiveCell.Formula= is right, and then check the "=IF" formula as well. Sure glad there are much smarter folks out there that know what to do.

Please Login or Register  to view this content.

Well, I finally figured this out, thanks to seeing something else on your website, actually. So the answer here is YES you must use the double quotes in the vba or you continue to get the "expected end of statement" error. However, because cjo told me to change it from R1C1, to activecell.Formula=, the formula copied over correctly WITH the double quotes pasting in as single quotes, whereas before it would not. So here is the corrected code and it works sweet as can be. I did take the TEXT function out of the original code line as I realized I didn't need it, the MID was working fine.

Please Login or Register  to view this content.