VBA to paste a formula

[Russk68]

Hi all
I am using the code below to replace the formula in a cell with a BeforeDoubleClick event but I am getting a syntax error. (Ex: If a cell is missing the formula, I just double click it to replace the formula)

This is the formula that I am trying to paste:
=IF(C4="",F3,IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI4,Instruments!AA$3:AA$68,0))+F3,""))

This formula is in the first cell and it needs to be relative when it is pasted in any cell in the column that I double click.

If Not Intersect(Target, Range("F:F")) Is Nothing Then 'change this column to suit
    Target.Formula = "=IF(C" & ActiveCell.Row & ="",F" & ActiveCell.Row & ,IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI" & ActiveCell.Row & ,Instruments!AA$3:AA$68,0))+F3,""))"
    Target.Offset(1, 0).Select
End If

I would like to be able to do it this way but I could probably just have code that just copies the formula from the cell above from where I double click.

Thank you in advance!

 

[Peter_SSs]

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

Copying from above is probably easier but if you want to persist, you are missing quite a few quote marks. It should be more like this, but there may also be an issue with some of the row numbers.

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

 

[Russk68]

Hi Peter!

It's very close to what I am looking for. The 4th formula down is the one that was pasted. Is it possible to make it all relative so that it fits in properly?

=IF(C7="",F6,IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI7,Instruments!AA$3:AA$68,0))+F6,""))
=IF(C8="",F7,IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI8,Instruments!AA$3:AA$68,0))+F7,""))
=IF(C9="",F8,IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI9,Instruments!AA$3:AA$68,0))+F8,""))
=IF(C10="",F10,IFERROR(INDEX(Instruments!Z$3:Z$68,MATCH(CI10,Instruments!AA$3:AA$68,0))+F3,""))

Thank you!

 

[Peter_SSs]

Try

Target.Formula = "=IF(C" & ActiveCell.Row & "="""",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]

That works!
Thank you very much Peter!!

 

[Peter_SSs]

That works!
Thank you very much Peter!!

You're welcome. Glad it was what you wanted.