VBA Insert a formula in a cell

[NDMDRB]

Hello,

I have a userform to add items to sheet1 and I'm using the code below,
and inserting manually formulas to columns ("C" "E" "F" "G")

Private Sub cmdAdd_Click()Dim ws As Worksheet
Dim MsgBoxResult As Long
Set ws = Sheet1
nr = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Cells(nr, 1) = CDbl(Me.txtBarCode)
ws.Cells(nr, 2) = Me.txtName
ws.Cells(nr, 4) = CDbl(Me.txtSellPrice)
MsgBoxResult = MsgBox("New Item has been added" & vbCrLf & vbCrLf & "Do you wanna add more?", vbYesNo)
If MsgBoxResult = vbNo Then
Unload Me
ElseIf MsgBoxResult = vbYes Then
txtBarCode = ""
txtName = ""
txtSellPrice = ""
Else
End If
End Sub

What I'm looking for is to insert the following formulas to:

• ws.Cells(nr, 3) / =IFERROR(AVERAGEIF(Purchase_BarCode,A2,Purchase_Cost),"0")
• ws.Cells(nr, 5) / =F2-G2
• ws.Cells(nr, 6) / =SUMIF(Purchase_BarCode,A2,Purchase_Qty)
• ws.Cells(nr, 7) / =SUMIF(Sales_BarCode,A2,Sales_Qty)

The above formulas reference to row 2, but I need them to be in the next available row

[TABLE="width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Bar Code[/TD]
[TD]Name[/TD]
[TD]Cost[/TD]
[TD]Sell Price[/TD]
[TD]Qty[/TD]
[TD]Purchase Qty[/TD]
[TD]Sales Qty[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Yongle]

Try this

    Dim f As String
    f = "=IFERROR(AVERAGEIF(Purchase_BarCode,A" & nr & ",Purchase_Cost),""0"")"
    ws.Cells(nr, 3).Formula = f
    f = "=F" & nr & "-G" & nr
    ws.Cells(nr, 5).Formula = f
    f = "=SUMIF(Purchase_BarCode,A" & nr & ",Purchase_Qty)"
    ws.Cells(nr, 6).Formula = f
    f = "=SUMIF(Sales_BarCode,A" & nr & ",Sales_Qty)"
    ws.Cells(nr, 7).Formula = f

 

[NDMDRB]

thank you so much Yongle this works very well

I have another inquiry in another userform, if you may help me

I have the following textboxes that calculate some values

txtAmount has a calculation which I have the code for it and all the below text boxes as well, unless the "txtPaid"

I need "txtPaid" to be the same as the "txtTotal" but to be able to change it's value
ex: if txtTotal.value = 100, then, txtPaid.value = 100, and then I can change the value of the txtPaid if I need

Private Sub txtDiscount_Change()Call txtTotal_Change
End Sub

Private Sub txtTotal_Change()
Me.txtTotal = Me.txtAmount - Me.txtDiscount
Call txtPaid_Change
Call txtRest_Change
End Sub


Private Sub txtPaid_Change()
Me.txtPaid.Text = Me.txtTotal 'I've tried this but I can't change the value
Call txtRest_Change
End Sub


Private Sub txtRest_Change()
Me.txtRest = Me.txtTotal - Me.txtPaid
End Sub

 

[Yongle]

Threads should stick to the original topic
- so I suggest you begin a new thread for that question