Need help with VBA

[Russk68]

Hi All
I am using the code below to paste the formula in a cell. I am so very close. I can't get ="" to paste.

The pasted formula that I'm trying to achieve: =IF(C4="",F3+CK3,IF(C3="",F3,IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI4,Instruments!AA$3:AA$68,0))+F3,"")))

I need C4="" and C3=""

Target.Formula = "=IF(C" & ActiveCell.Row & "="",F" & ActiveCell.Row - 1 & "+ CK" & ActiveCell.Row - 1 & ",If(C" & ActiveCell.Row - 1 & "="",F" & ActiveCell.Row - 1 & ",IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI" & ActiveCell.Row & ",Instruments!AA$3:AA$68,0))+F" & ActiveCell.Row - 1 & ",""""))"

The above code results: =IF(C4=",F3+ CK3,If(C3=",F3,IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI4,Instruments!AA$3:AA$68,0))+F3,""))

Target.Formula = "=IF(C" & ActiveCell.Row & "="""",F" & ActiveCell.Row - 1 & "+ CK" & ActiveCell.Row - 1 & ",If(C" & ActiveCell.Row - 1 & "="""",F" & ActiveCell.Row - 1 & ",IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI" & ActiveCell.Row & ",Instruments!AA$3:AA$68,0))+F" & ActiveCell.Row - 1 & ",""""))"

The above code with double "" results in a runtime error "Method formula of object range failed.



I thought for sure this would work:

Target.Formula = "=IF(C" & ActiveCell.Row & "=""" & ",F" & ActiveCell.Row - 1 & "+ CK" & ActiveCell.Row - 1 & ",If(C" & ActiveCell.Row - 1 & "=""" & ",F" & ActiveCell.Row - 1 & ",IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI" & ActiveCell.Row & ",Instruments!AA$3:AA$68,0))+F" & ActiveCell.Row - 1 & ",""""))"

The above code results: =IF(C4=",F3+ CK3,If(C3=",F3,IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI4,Instruments!AA$3:AA$68,0))+F3,""))

Again, I need C4="" and C3=""

Spent 2 hrs trying to figure this out.

Thanks in advance! Taking a long break.

 

[jkpieterse]

You should double each " that is to be visible in the formula. So if the formula contains "", your VBA should show """" at that location. This does not include the starting and ending quotes of the string expression itself. TO display a messagebox showing "", you need to add two additional "'s like so:

MsgBox """"""

So your formula becomes:

Target.Formula = "=IF(C" & ActiveCell.Row & "="""",F" & ActiveCell.Row - 1 & "+ CK" & ActiveCell.Row - 1 & ",If(C" & ActiveCell.Row - 1 & "="""",F" & ActiveCell.Row - 1 & ",IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI" & ActiveCell.Row & ",Instruments!AA$3:AA$68,0))+F" & ActiveCell.Row - 1 & ",""""))"

 

[Russk68]

Ah!
I thought the double quotes needed quotes. I get it now!

Thank you very much!!

Russ

 

[Russk68]

Oops!
I thought it worked but its giving meC4= """

Target.Formula = "=IF(C" & ActiveCell.Row & "="""""",F" & ActiveCell.Row - 1 & "+ CK" & ActiveCell.Row - 1 & ",If(C" & ActiveCell.Row - 1 & "="""""",F" & ActiveCell.Row - 1 & ",IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI" & ActiveCell.Row & ",Instruments!AA$3:AA$68,0))+F" & ActiveCell.Row - 1 & ",""""))"

Result:=IF(C4=""",F3+ CK3,If(C3=""",F3,IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI4,Instruments!AA$3:AA$68,0))+F3,""))

Thanks!

 

[jkpieterse]

You've added too many " and one ) too little this is correct:

Target.Formula = "=IF(C" & ActiveCell.Row & "="""",F" & ActiveCell.Row - 1 & "+ CK" & ActiveCell.Row - 1 & ",If(C" & ActiveCell.Row - 1 & "="""",F" & ActiveCell.Row - 1 & ",IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI" & ActiveCell.Row & ",Instruments!AA$3:AA$68,0))+F" & ActiveCell.Row - 1 & ","""")))"

 

[Russk68]

I did notice that I was missing a ). I thought that was strange. I thought each " needed "" so I was doing """""" and then added the (.
Your fix is works!


I thought this was weird.
As a result from the old code, this is the formula that was in the cell with a missing ) and it didn't give me an error.
IF(C4=""",F3+ CK3,If(C3=""",F3,IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI4,Instruments!AA$3:AA$68,0))+F3,""))

Thanks again!