[Resolved] [Excel] Problem inserting formula into a cell in VBA

[SteveA]

Hi

I have a nice complicated Excel formula which converts the following types of numbers (text formatted) to correctly formatted phone numbers

414486515 To 0414 486 515
262935739 To 02 6293 5739
62951225 To 02 6295 1225
385427075 To 03 8542 7075
1300425884 To 1300 425 884
1800000000 To 1800 000 000
131126 To 13 11 26

=IF(LEN(RC[1])=6,LEFT(RC[1],2)&" "&MID(RC[1],3,2)&" "&RIGHT(RC[1],2),IF(LEFT(RC[1],2)="04",LEFT(RC[1],4)&" "&MID(RC[1],5,3)&" "&RIGHT(RC[1],3),IF(LEFT(RC[1],1)="4","0"&LEFT(RC[1],3)&" "&MID(RC[1],4,3)&" "&RIGHT(RC[1],3),IF(LEFT(RC[1],1)="2","0"&LEFT(RC[1],1)&" "&MID(RC[1],2,4)&" "&RIGHT(RC[1],4),IF(LEFT(RC[1],1)="3","0"&LEFT(RC[1],1)&" "&MID(RC[1],2,4)&" "&RIGHT(RC[1],4),IF(LEFT(RC[1],1)="6","02 "&LEFT(RC[1],4)&" "&RIGHT(RC[1],4),IF(LEFT(RC[1],1)="7","0"&LEFT(RC[1],1)&" "&MID(RC[1],2,4)&" "&RIGHT(RC[1],4),IF(LEFT(RC[1],1)="8","0"&LEFT(RC[1],1)&" "&MID(RC[1],2,4)&" "&RIGHT(RC[1],4),IF(LEFT(RC[1],1)="1",LEFT(RC[1],4)&" "&MID(RC[1],5,3)&" "&RIGHT(RC[1],3),"Other")))))))))

Yes it’s nasty, but it works.

What I want to do is automate the process and I’ve built a macro to do that but it fails.

I select the first cell of the phone # column (Col 1). The macro inserts a column to the right (Col 2) and copies the data from Col 1 to Col 2, and removes any spaces, hyphens etc from the numbers.
That bit works fine.

The macro should then insert the formula above into the first phone # cell of Col 1,
That’s where I have the problem.

It will then fill down Col 1 and copy Col 1 and Paste values over Col 1, then delete Col 2.
That bit will work.

Pasting in the formula is causing problems. I got the macro recorder to paste in the value 12345 into the right place, opened the VB for the macro and replaced the line:
ActiveCell.FormulaR1C1 = "12345"
With:
ActiveCell.FormulaR1C1 = "=IF(LEN(RC[1])=6,L .......etc"

But I get a compile error. - Expected end of statement.

The formula works on its own, the VBA ‘works’ on its own but not when I put them together.

I had a look around the forums and found that what I'm doing should work, but it isn't.

Is there some limit to the length of a formula I can insert into the VBA?

Or have I just tried to do something very stupid?

[westconn1]

all " characters within your formula have to be escaped

that is use char(34)
or escape with leading " like ""
for simplicity you could try

ActiveCell.FormulaR1C1 = replace("=IF(LEN(RC[1])=6,L .......etc", """", """""")  ' replace all " with ""

[SteveA]

Thank you Westconn1!!

As soon as I read the post I remembered the "". But I'd just taken a day banging my head on the desk trying to find the problem.

I guess the answer is often obvious when someone points it out!

My macro works perfectly. Well almost. There are always other formats I could check for, but since this does 99% of the ones I use, I'm happy!

Thank you again!

SteveA