VBA insert formula

[Russk68]

Hi all!

At times it is necessary to overwrite a formula in a cell. I would like a simple way to replace the formula when necessary. I would like to double click in the cell to replace the formula.

Row 3 is the first row where this formula starts. It would need to be relative.

=IFERROR(INDEX(Instruments!$AW$3:$AW$40,MATCH('Patch By UNIVERSE'!A3,Instruments!$A$3:$A$40,1)),"")

Thank you!

 

[Michael M]

why not simply replace ALL the formulas in the column.
This assumes the formula is going in Col "A"

Sub MM1()
Dim lr As Long
lr = Cells(Rows.Count, "B").End(xlUp).Row
Range("A3:A" & lr).Formula = "=IFERROR(INDEX(Instruments!$AW$3:$AW$40,MATCH('Patch By UNIVERSE'!A3,Instruments!$A$3:$A$40,1)),"""")"
End Sub

 

[Russk68]

Hi Mike

Because there are many cells in the column where I would not want to replace the formulas.

If possible, I would like it to be a BeforeDoubleClick event, so when I double click in a cell, only that cell is pasted with the formula.

Thanks!

 

[Michael M]

Maybe this then
change the column in the Intersect line to suit your needs

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("B:B")) Is Nothing Then 'change this column to suit
    Target.Formula = "=IFERROR(INDEX(Instruments!$AW$3:$AW$40,MATCH('Patch By UNIVERSE'!A" & ActiveCell.Row & ",Instruments!$A$3:$A$40,1)),"""")"
    Target.Offset(1, 0).Select
End If
End Sub

 

[Russk68]

Hi Mike

That worked great!
But I tried to add another range (K:K) below with a different formula and I got Run time error 1004 Method formula of object range failed. The formula is highlighted in yellow.
I merged you code into other existing code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim c As Variant:    c = Split(Target.Address, "$")(1)
    Select Case c
        Case "A"
            Cancel = True
            Target.End(xlUp).AutoFill Range(Target.End(xlUp), Target), xlFillSeries
    End Select
    
If Not Intersect(Target, Range("c:c")) Is Nothing Then 'change this column to suit
    Target.Formula = "=IFERROR(INDEX(Instruments!$AW$3:$AW$40,MATCH('Patch By UNIVERSE'!A" & ActiveCell.Row & ",Instruments!$A$3:$A$40,1)),"""")"
    Target.Offset(1, 0).Select
End If
If Not Intersect(Target, Range("K:K")) Is Nothing Then 'change this column to suit
    Target.Formula = "=IFERROR(IF(G9=0,0,(INDEX('Multi Build'!A$3:A$522,MATCH('Patch By UNIVERSE'!G9,'Multi Build'!H$3:H$522,0)))),"")"
    Target.Offset(1, 0).Select
End If


End Sub

 

[Michael M]

Try this one....but do you want the "K:K" formula to be relative to the target row ???

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim c As Variant:    c = Split(Target.Address, "$")(1)
    Select Case c
        Case "A"
            Cancel = True
            Target.End(xlUp).AutoFill Range(Target.End(xlUp), Target), xlFillSeries
    End Select
    
If Not Intersect(Target, Range("c:c")) Is Nothing Then 'change this column to suit
    Target.Formula = "=IFERROR(INDEX(Instruments!$AW$3:$AW$40,MATCH('Patch By UNIVERSE'!A" & ActiveCell.Row & ",Instruments!$A$3:$A$40,1)),"""")"
    Target.Offset(1, 0).Select
End If
If Not Intersect(Target, Range("K:K")) Is Nothing Then 'change this column to suit
    Target.Formula = "=IFERROR(IF(G9=0,0,(INDEX('Multi Build'!A$3:A$522,MATCH('Patch By UNIVERSE'!G9,'Multi Build'!H$3:H$522,0)))),"""")"
    Target.Offset(1, 0).Select
End If


End Sub

 

[Russk68]

Yes, I do want K:K to be relative.

Thanks!

 

[Michael M]

this one then

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim c As Variant:    c = Split(Target.Address, "$")(1)
    Select Case c
        Case "A"
            Cancel = True
            Target.End(xlUp).AutoFill Range(Target.End(xlUp), Target), xlFillSeries
    End Select
    
If Not Intersect(Target, Range("c:c")) Is Nothing Then 'change this column to suit
    Target.Formula = "=IFERROR(INDEX(Instruments!$AW$3:$AW$40,MATCH('Patch By UNIVERSE'!A" & ActiveCell.Row & ",Instruments!$A$3:$A$40,1)),"""")"
    Target.Offset(1, 0).Select
End If
If Not Intersect(Target, Range("K:K")) Is Nothing Then 'change this column to suit
    Target.Formula = "=IFERROR(IF(G" & activecell.row & "=0,0,(INDEX('Multi Build'!A$3:A$522,MATCH('Patch By UNIVERSE'!G" & activecell.row & ",'Multi Build'!H$3:H$522,0)))),"""")"
    Target.Offset(1, 0).Select
End If


End Sub

 

[Russk68]

That works!
This is awesome!

Thank you very much!!

 

[Michael M]

Glad to help and thx for the feedback...