PDA

View Full Version : Add cells until adjacent cell is not blank



mlindenbach
06-13-2011, 08:35 AM
Hello,

I have four excel spreadsheets that give a summary of all invoices generated throughout the years. The sheets are 7000 rows each and I need a code that will help me. Right now the spreadsheet outlines the invoice number and invoice total on one line and below that line is each line item identifying the products sold. The detailed line items below the invoice total outlines the Kg's sold to the customer; however, the invoice total does not tally up the total Kg’s (which I need to know). I need a code that will add the Kg's and place it in the invoice total. I have attached a small version of the file for your reference. The highlighted lines indicate where I need the total to sit.

- Melissa

JKwan
06-13-2011, 02:36 PM
OK, give this a try:

Sub CalcTotalWeight()
Dim LastRow As Long
Dim lRow As Long
Dim DataSheet As Worksheet
Dim InvoiceNumber As String
Dim TotalWeight As Long
Dim InvoiceRow As Long

Const StartRow As Long = 3

Set DataSheet = Worksheets("Sheet1")

LastRow = FindLastRow(DataSheet, "I")
For lRow = StartRow To LastRow
InvoiceNumber = DataSheet.Cells(lRow, "B")
If InvoiceNumber <> "" Then
DataSheet.Cells(InvoiceRow, "F") = TotalWeight
InvoiceRow = lRow
TotalWeight = 0
Else
TotalWeight = TotalWeight + DataSheet.Cells(lRow, "F")
End If
Next lRow

Set DataSheet = Nothing
End Sub
Public Function FindLastRow(ByVal WS As Worksheet, ColumnLetter As String)
' This function will fine the last row based on the Column that is sent to it.
FindLastRow = WS.Range(ColumnLetter & "65536").End(xlUp).Row
End Function

mlindenbach
06-13-2011, 03:12 PM
Hummmmm Thanks for your reply but there was an error - when I put the code in the Sheet I get a 400 error. I posted it in a Module and I get the application defined error and the break is at

DataSheet.Cells(InvoiceRow, "F") = TotalWeight

Any ideas?

Melissa

JKwan
06-13-2011, 07:26 PM
I am guessing:
Set DataSheet = Worksheets("Sheet1")
Is "Sheet1" the name of the sheet? If not, change it

mlindenbach
06-13-2011, 07:40 PM
Hello - no that isn't it. I initially thought that as well but it didn't work when I changed "sheet1" to the title of the tab and I also plugged it into the example I originally posted and it didn't work on th example either.

Thanks - looking forward to your help!

JKwan
06-14-2011, 05:58 AM
Well, here is my example copy that you posted, along with my code.

JKwan
06-14-2011, 06:18 AM
Sorry, found my mistake:
Sub CalcTotalWeight()
Dim LastRow As Long
Dim lRow As Long
Dim DataSheet As Worksheet
Dim InvoiceNumber As String
Dim TotalWeight As Long
Dim InvoiceRow As Long

Const StartRow As Long = 3

Set DataSheet = Worksheets("Sheet1")

LastRow = FindLastRow(DataSheet, "I")
For lRow = StartRow To LastRow
InvoiceNumber = DataSheet.Cells(lRow, "B")
If InvoiceNumber <> "" Then
DataSheet.Cells(IIf(InvoiceRow = 0, StartRow, InvoiceRow), "F") = TotalWeight
InvoiceRow = lRow
TotalWeight = 0
Else
TotalWeight = TotalWeight + DataSheet.Cells(lRow, "F")
End If
Next lRow

Set DataSheet = Nothing
End Sub
Public Function FindLastRow(ByVal WS As Worksheet, ColumnLetter As String)
' This function will fine the last row based on the Column that is sent to it.
FindLastRow = WS.Range(ColumnLetter & "65536").End(xlUp).Row
End Function

mlindenbach
06-14-2011, 08:02 AM
Hi! YEAAAA it worked perfectly and quickly! Thank you so much for your help. I now don't have to go through 30,000 lines to add stuff up.

You rock!

JKwan
06-14-2011, 09:05 AM
Glad it worked!