VBA insert formula

Russk68

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

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
why not simply replace ALL the formulas in the column.
This assumes the formula is going in Col "A"

Code:
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
 
Last edited:
Upvote 0
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!
 
Upvote 0
Maybe this then
change the column in the Intersect line to suit your needs

Code:
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
 
Upvote 0
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.
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
 
Last edited:
Upvote 0
Try this one....but do you want the "K:K" formula to be relative to the target row ???

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
 
Upvote 0
this one then

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(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
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,212
Members
452,618
Latest member
Tam84

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