Russk68

Well-known Member
Joined
May 1, 2006
Messages
589
Office Version
  1. 365
Platform
  1. MacOS
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=""

Code:
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,""))

Code:
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:
Code:
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.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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:
Code:
MsgBox """"""
So your formula becomes:
Code:
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 & ",""""))"
 
Upvote 0
Ah!
I thought the double quotes needed quotes. I get it now!

Thank you very much!!

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

Code:
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!
 
Upvote 0
You've added too many " and one ) too little :) this is correct:
Code:
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 & ","""")))"
 
Last edited:
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top