VBA Userform, Sum range(s)

[bologne]

Good Morning,

I have the following code:

Private Sub SaleNumber_AfterUpdate()
Dim jRow As Range
Dim xRow As Range
Dim rRow As Ranges
Dim ws As Worksheet
Set ws = Worksheets("Sales")
Set jRow = Sheets("Sales").Range("$B:$B").Rows.Find(what:=Me.SaleNumber.Value, LookIn:=xlFormulas, lookat:=xlWhole)
Set rRow = Sheets("Sales").Range("$B:$B").Rows.Find(what:=Me.SaleNumber.Value, LookIn:=xlFormulas, lookat:=xlWhole)
If jRow Is Nothing Then
'empty
Me.SaleNumber.SetFocus
  MsgBox "This is not a valid sale number"
  Exit Sub
Else
Me.SaleDate.Value = ws.Cells(jRow.Row, 4)
Me.cID.Value = ws.Cells(jRow.Row, 3)
Me.Amount.Value = Sum(ws.Cells(rRow.Row, 6))

End If

End Sub

My "rRow"'s purpose is to find row numbers where Me.Salenumber.Value = a value inserted in my userform.

Upon updating, I then want another userfield within the userform to display the total value of the sale, which is in row 6.

As an example of this, I have put

Me.Amount.Value = Sum(ws.Cells(rRow.Row, 6))

As I'm sure you're aware, this is not a valid formula. I was just looking for some direction as to how I could rectify this.

Best Regards
Ralph

 

[Sektor]

ws.Cells(rRow.Row, 6) is one cell. What range you wanna sum?

 

[bologne]

ws.Cells(rRow.Row, 6) is one cell. What range you wanna sum?

Basically, I want to sum all cells in column 6, that match "rRow".

Say I have SaleID in Column 1, which rRow checks for, and Sale Amount in column 6.

Several rows could have the same SaleID.

If I have 3 rows with SaleID "20", each with Sale Amount "100", I would like to total these and show it in my userform as "300".

I hope that makes sense.

All the best

 

[bologne]

Sorry for being confusing.

I will attempt to summarise my situation as clearly as I can.

My Spreadsheet:
The purpose of my spreadsheet is to act as an inventory / invoice producing system.

My "Invoice" sheet and "Create Invoice" userform:
My invoice sheet is a template within my spreadsheet.
The Create Invoice userform is used to populate the Invoice sheet, and then (not yet implemented) save it as a PDF file, keeping a digital copy of all invoices.

My issue:
Within the Create Invoice userform, the user inserts a saleID. This pertains to a transaction that they wish to create the invoice for. Upon updating this field on the userform (Me.Salenumber), most of the other fields are updated with information relating to the sale, in order that the user may check these, to verify they have entered the correct sale ID.

One of these values is the sale value. The sale value must be the total sale price of all items sold within that transaction. This is mirrored in my userform field "Me.Amount".

If looking at the "Sales" sheet, you are able to see Sale ID in column B, and Payment Amount in column F. However, the issue arises in that some Sales are for more than one item. If there are 3 entries for "Sale ID" 4, each for £20, then I would need the "Me.Amount" user field to read £60.

Within the VBA macro code, I have the following to try and work this out:

Dim rRow As Ranges
Dim ws As Worksheet
Set ws = Worksheets("Sales")
Set rRow = Sheets("Sales").Range("$B:$B").Rows.Find(what:=Me.SaleNumber.Value, LookIn:=xlFormulas, lookat:=xlWhole)

Me.Amount.Value = sum(ws.Cells(rRow.Row, 6))

My intent with the above code is to find all cases where SaleID (column B in my sales sheet) is the SaleNumber inserted in the userform.

I then intended to sum the value of the sale for each of these cases. Namely, column 6.

I hope this helps explain.

Best Regards
Ralph Lawrence