How do I sum without knowing the range?

[sGraham24]

Hello. I have this code (see below) creating the report I have attached a picture of (I cannot install XL2BB, on my network). I need to sum the hours based on the PROJECT and EMPLOYEE LEVEL (as you can see I have them shaded to show the differences as the project numbers are sometimes the same. This report will be run weekly, and the ranges will change as the PROJECT and EMPLOYEE LEVEL changes, therefore I cannot reference a range. I would like the total to go where the TOTAL is placed. I am at a loss; how do I sum the totals not having a set range?


Sub FindSLIN()

Dim End_Row As Long
Dim Range As Range
Dim NumRows As Integer
Dim Address As String
Dim CurrentRow As Integer
Dim slin As String
Dim SLINReportRow As Integer

' Select the "Sheet 1" sheet as the active sheet
Sheets("Sheet 1").Select

' go find the end row. Start with a1, a2, a3 etc. until you find the first empty row
NumRows = 1

Do
If (Cells(NumRows, 1).Text = "") Then
Exit Do
End If
Cells(NumRows, 26).Value = 0
NumRows = NumRows + 1
Loop

' Now I know the rows I need to search
CurrentRow = 2
SLINReportRow = 1

Do
If Cells(CurrentRow, 26).Value = 0 Then
slin = GetSLIN(CurrentRow)
If Not slin = "" Then

'Harvest data and populate SLINReport
Call CopyDatatoSLINReport(CurrentRow, SLINReportRow, slin, Sheets("Sheet 1").Cells(CurrentRow, 20).Text)

End If
End If

CurrentRow = CurrentRow + 1
Loop Until CurrentRow = NumRows

' Time to sort
Address = "A1:D" & NumRows

Sheets("SLINReport").Select
Columns("A:D").Sort Key1:=Columns("A"), Order1:=xlAscending, Key2:=Columns("B"), Order2:=xlDescending, Header:=xlNo

'Range("A2", Range("D" & Rows.Count).End(xlUp)).Sort [A2], xlAscending
'Sheets("SLINReport").Range("A1:A100").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo

End Sub

'
' This procedure will move data from sheet 1 to SLINReport
'
Sub CopyDatatoSLINReport(ByVal Row As Integer, ByRef SLINReportRow As Integer, ByVal SearchSlin As String, ByVal LaborCat As String)

Dim slin As String

'SLIN will be in column A
'Labor Category will be in Column B
'Hours will be in Column C
'Name will be in Column D

Do
slin = GetSLIN(Row)
If ((slin = SearchSlin) And (Sheets("Sheet 1").Cells(Row, 20).Text = LaborCat)) Then
Sheets("Sheet 1").Cells(Row, 26).Value = 1
Sheets("SLINReport").Cells(SLINReportRow, 1).Value = slin
Sheets("SLINReport").Cells(SLINReportRow, 2).Value = Sheets("Sheet 1").Cells(Row, 20).Text
Sheets("SLINReport").Cells(SLINReportRow, 3).Value = Sheets("Sheet 1").Cells(Row, 12).Value
Sheets("SLINReport").Cells(SLINReportRow, 4).Value = Sheets("Sheet 1").Cells(Row, 3).Text + " " + Sheets("Sheet 1").Cells(Row, 4).Text
SLINReportRow = SLINReportRow + 1
End If

Row = Row + 1
Loop Until Sheets("Sheet 1").Cells(Row, 1).Text = ""

End Sub

'
' This function will find and return the SLIN of the specified Row
'

Function GetSLIN(Row As Integer) As String

Dim SearchString As String
Dim slin As String

slin = ""
SearchString = Cells(Row, 13).Text & Cells(Row, 14).Text & Cells(Row, 15).Text & Cells(Row, 16).Text & Cells(Row, 17).Text

'Next Step is to search for SLIN
If (InStr(SearchString, "SLIN") > 0) Then
slin = Mid$(SearchString, InStr(SearchString, "SLIN") + 4)
ElseIf (InStr(SearchString, "CLIN") > 0) Then
slin = Mid$(SearchString, InStr(SearchString, "CLIN") + 4)
Else
'MsgBox ("SLIN/CLIN not found" + SearchString)
Sheets("Sheet 1").Cells(Row, 26).Value = 1
GetSLIN = ""
End If

' Must be a ' ' at the end of the SLIN, so find it and return just the SLIN
slin = LTrim(slin)
If (InStr(slin, ")") > 0) Then
slin = Left$(slin, InStr(slin, ")") - 1)
End If
If (InStr(slin, " ") > 0) Then
slin = Left$(slin, InStr(slin, " ") - 1)
End If
slin = Trim(slin)
GetSLIN = slin
End Function

 

[sGraham24]

I have added in an empty row after the sorting happens and can total everything above the empty row but when I run it again it doesn't start over at 0 to total the next group. Does anyone know how to reset the value to 0 so it sums each group seperately?

'This will add a blank row after the data is sorted
'
Sub AddRow()

Dim iRow As Integer
Dim iCol As Integer
Dim oRng As Range

Set oRng = Range("a1")

iRow = oRng.Row
iCol = oRng.Column

Do
'
If Cells(iRow + 1, iCol) <> Cells(iRow, iCol) Then
Cells(iRow + 1, iCol).EntireRow.Insert shift:=xlDown
iRow = iRow + 2
Else
iRow = iRow + 1
End If
'
Loop While Not Cells(iRow, iCol).Text = ""
'
End Sub

' This will look for the blank row and sum the numbers before it.
'
Sub SumTotals()
Dim ws As Worksheet
Set ws = Worksheets("SLINReport")

Dim FirstCell As Range
Set FirstCell = ws.Range("C1")

'get very last cell as stop criteria
Dim VeryLastCell As Range
Set VeryLastCell = ws.Cells(ws.Rows.Count, "End").End(xlUp)

Do
Dim LastCell As Range
If FirstCell.Offset(1) = vbNullString Then 'test if there is only one cell to sum
Set LastCell = FirstCell
Else
Set LastCell = FirstCell.End(xlDown)
End If

'this is the cell where the total will write to
With LastCell.Offset(1, 0)
.Value = Application.WorksheetFunction.SUM(ws.Range(FirstCell, LastCell))
.Interior.Color = RGB(224, 224, 224)
End With

Set Value = 0
Set FirstCell = LastCell.Offset(2, 0)

Loop While FirstCell.Row < VeryLastCell.Row
End Sub