Insert formula into cell works until I introduce the formula I actually want (it has " and *)

[geewhysee]

I'm trying to automate taking a phone number and blanking out the first 7 digits, so 07779287640 becomes *******7640. As an additional complication the phone number needs to be stored with " at the front and back of it and sometimes there is no phone number so I'll have to return "". I have the phone number in column H and my manual formula (which works) is

=IF((H1<>""""""),""""&"*******"&RIGHT(H1,5),H1)

I have created a macro to get the number in the correct format in row I (which I will then paste values back into H).

Sub rowI()
Dim LastRow As Long
Set sht = ActiveSheet
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

Range("I1:I" & LastRow).Formula = "=IF((H1<>""""""),""""&"*******"&RIGHT(H1,5),H1)"

End Sub

But I get the error

Compile Error
Syntax Error

I assume this has something to do with the " and * throwing the compiler for a loop. when I simplify things and use

Sub rowI()
Dim LastRow As Long
Set sht = ActiveSheet
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

Range("I1:I" & LastRow).Formula = "H1"

It works fine copying the number from the adjacent column exactly. Rather than go through a bunch of trial and error I thought it was worth asking if anyone here knows what I'm doing wrong.

 

[Norie]

Try this.

Range("I1:I" & LastRow).Formula = "=IF(H2<>"""""""""""",""""""""&""*******""&RIGHT(H2,5),H2)"

PS Why do you have H2<>""""""? All you should need to check for an empty cell is H2<>"".

 

[geewhysee]

Try this.

Range("I1:I" & LastRow).Formula = "=IF(H2<>"""""""""""",""""""""&""*******""&RIGHT(H2,5),H2)"

PS Why do you have H2<>""""""? All you should need to check for an empty cell is H2<>"".

That works. Thanks so much. The cell doesn't have a number but still has "" in it sorry if that wasn't clear.

 

[Special-K99]

Try

Range("I1:I" & LastRow).Formula =  "=CHAR(34)&IF(H1<>"""",""*******""&RIGHT(H1,4),"""")&CHAR(34)"