Results 1 to 4 of 4

Thread: VBA Insert a formula in a cell
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2016
    Posts
    125
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Insert a formula in a cell

    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")

    Code:
    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

    A B C D E F G
    1 Bar Code Name Cost Sell Price Qty Purchase Qty Sales Qty
    2
    3
    4

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,055
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: VBA Insert a formula in a cell

    Try this

    Code:
        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

  3. #3
    Board Regular
    Join Date
    Jun 2016
    Posts
    125
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Insert a formula in a cell

    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


    Code:
    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

  4. #4
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,055
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: VBA Insert a formula in a cell

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •