Auto-fill a column with formula using VBA

Question

I am trying to create a basic Qty x Unit Price = Extended Price formula across 3 columns, where I can enter either the Unit Price or the Extended Price and the other will auto calculate. I have tried a basic VBA code based on this really helpful thread](https://answers.microsoft.com/en-us/msoffice/forum/all/how-to-get-a-cell-to-display-a-default-value/8f4e341d-7615-4706-914a-0cd19e2e1c92 "https://answers.microsoft.com/en-us/msoffice/forum/all/how-to-get-a-cell-to-display-a-default-value/8f4e341d-7615-4706-914a-0cd19e2e1c92") and answer by [Shane Devenshire as follows that reinserts the Unit Price formula if the entered price is deleted, but is throwing an error.

It must also retain manually entered figures.

This is what I have:

This is overwriting all the cells in the range, and also throws an error: "Runtime error 13, Type mismatch."

I am now well out of my scope of knowledge, so really simple suggestions / answers would be great please.

Answer 1

Let's say Qty is in L2 and down, Unit Price in M2 and down, and Extended Price in N2 and down.

Right-click the sheet tab and select View Code from the context menu.

Delete the code that you have, and paste the following code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Long
    If Target.CountLarge > 1 Then Exit Sub
    If Not Intersect(Range("M2:M" & Rows.Count), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        r = Target.Row
        Range("N" & r).Formula = "=L" & r & "*M" & r
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
    If Not Intersect(Range("N2:N" & Rows.Count), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        r = Target.Row
        Range("M" & r).Formula = "=N" & r & "/L" & r
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Answer 2

Thank you HansV

That works perfectly and exactly as described. This is very close to what

unfortunately I overlooked the fact I have a multiple nested IF formula in your example column N, hence why I was getting my result from a workaround from an adjacent column to prevent a circular reference.

How do I tweak your code to apply to only 1 column, with a formula that include brackets: =N2/(1-M2)

Many thanks,

Answer 3

Can you tell or show what the relevant columns and formulas are?

Answer 4

Thank you,

Here's the formulas for the relevant columns. Column D contains the Quantity. Column I contains a calculated area if dimensions are entered.

K	L	M	N
Rate	Sell	GM	Item Cost
=N7/(1-M7)	=ROUND(IF(K7<>0,MAX(D7,1)*IF(J7="m2",I7,1)*K7,MAX(D7,1)*N7/(1-M7)),0)	0.35	=IF($O7="Sell",SUMIFS($S$7:$S$191,$A$7:$A$191,$A7))

Answer 5

Here's the final version; I hope someone else finds it useful.

I needed to re-add the line to check if the cell was empty.

If Target.Value = "" Then 

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Long
    If Target.CountLarge > 1 Then Exit Sub
    If Not Intersect(Range("K7:K" & Rows.Count), Target) Is Nothing Then
        If Target.Value = "" Then
            Application.ScreenUpdating = False
            Application.EnableEvents = False
            r = Target.Row
            Range("K" & r).Formula = "=N" & r & "/(1-M" & r & ")"
            Application.EnableEvents = True
            Application.ScreenUpdating = True
        End If
    End If
End Sub

Thanks HansV MVP for setting me on the right track.