adding "SUM" formula for selected cell in Userform VBA

[Faiz Abdat]

Hi everyone,

I'm working now with a userform in VBA, in which i can input a data using that form to other sheet
here's the code that i used, the last code won't work as I wish,
the column "N" should total column "K", "L", and "M" in each row..
but i didn't work that way,

if anyone knows on what part that code is incorrect, please let me know

really appreciate you help

thank you

Private Sub postrecord_Click()
    ' find the next row on the data sheet
    Dim Nextrow, sumrow As String
    Nextrow = Worksheets("Invoice").Cells(Rows.Count, "A").End(xlUp).Row + 1
    sumrow = Worksheets("Invoice").Cells(Rows.Count, "K").End(xlUp)
    
    Dim salesAmt, vat, wht As Double
        salesAmt = Cells(sumrow - 1, "K")
        vat = Cells(sumrow - 1, "L")
        wht = Cells(sumrow - 1, "M")

    'Write the values from this form to that row
    With Worksheets("Invoice")
    'enter date and time stamp in record
        With .Cells(Nextrow, "A")
             .Value = Now
             .NumberFormat = "mm/dd/yyyy hh:mm:ss"
        End With
             
             .Cells(Nextrow, "B").Value = Application.UserName  'enter user name in column B
             .Cells(Nextrow, "C").Value = Me.Cb_companyname 'company name
             .Cells(Nextrow, "D").Value = Me.tb_Invoicenumber 'invoice number
             With .Cells(Nextrow, "E")
                  .Value = Me.Cb_salesperiod.Text 'year of sales
                  .NumberFormat = ("###")
             End With
                          
             .Cells(Nextrow, "F").Value = Me.tb_invreceived 'month of sales
             
             If Me.ob_fob = True Then   'FOB??
                 .Cells(Nextrow, "G").Value = "FOB"
                    Else
                 .Cells(Nextrow, "G").Value = ""
             End If
             
             If Me.ob_cif = True Then   'CIF??
                .Cells(Nextrow, "G").Value = "CIF"
                    Else
                .Cells(Nextrow, "G").Value = ""
             End If
             
             .Cells(Nextrow, "H").Value = Me.Cb_salesproduct 'CPO, PK or FFB??
             
             With .Cells(Nextrow, "I")
                  .Value = Me.tb_qty.Text
                  .NumberFormat = "#,###0.0"
             End With
             With .Cells(Nextrow, "J")
                  .Value = Me.tb_price.Text
                  .NumberFormat = "#,###0.0"
             End With
             
             With .Cells(Nextrow, "K") 'Sales Amount
                  .Value = Val(tb_qty.Text) * Val(tb_price.Text)
                  .NumberFormat = "#,###0.0"
             End With
             
            If Me.Cb_VAT = True Then   'add VAT 10%??
                 .Cells(Nextrow, "L").Value = Val(tb_qty.Text) * Val(tb_price.Text) * 0.1
                    Else
                 .Cells(Nextrow, "L").Value = 0
             End If
             
             If Me.Cb_WHT = True Then   'add WHT 0.25%??
                .Cells(Nextrow, "M").Value = Val(tb_qty.Text) * Val(tb_price.Text) * -0.0025
                    Else
                .Cells(Nextrow, "M").Value = 0
             End If
                  
        .Cells(Nextrow, "N").Value = (salesAmt + vat + wht) 'Total Invoice Amount
     
     End With
     
    'Unload the form
    Unload Me

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

Not sure, but there are two things you should check:

1. The sumrow calculation does not look right. Should there be a .Row at the end of that line?

sumrow = Worksheets("Invoice").Cells(Rows.Count, "K").End(xlUp).Row

2. You are recording the values of K:M at the start of the code but change it later on. That means that the quantity summed will be the original value and not the final value in those cells. You could use:

.Cells(Nextrow, "N").Value = Application.Sum(.Range("K1:M1").Offset(Nextrow - 1))

to perform the summation, instead.


I hope this helps,

 

[Faiz Abdat]

Thanks Rick,

really appreciate your help, I'll try to add that in my code

 

[Faiz Abdat]

That code works really awesome, thanks a lot

if I may know, how do you learn vba & macro? cause I've tried to find it on the internet, but nothing I could find a reference that quite comprenhensif

for beginner like me..

 

[RickXL]

No problem and thanks for the feedback.

I found quite a good book on the Internet, once. OK, it is a bit out of date now but most of it still applies. https://grabebook.files.wordpress.com/2011/12/excel-2007-vba-programmers_reference.pdf

One of our MrExcel members produces a useful list of resources every so often. I am not sure if this is the most recent: https://www.mrexcel.com/forum/excel-questions/910633-learning-vba.html

I hope this helps,