im trying to get totals from combined worksheet and show the results on the summary sheet using VBA. It needs to be VBA for the assessment meaning I cant use sum/sumIf. I have done some code but it all appears as 0 on the summary Worksheet. I'm not sure exactly what I'm getting wrong whether its the range name or some else
the totals part of the vba code. I will also include the full code below. i am also open to a different approach if you think that may be easier/better.
Code
Do While ActiveCell <> ""
If ActiveCell.Value = "D" Then
DrummersTotal = DrummersTotal + 1
ElseIf ActiveCell.Value = "S" Then
SweepsTotal = SweepsTotal + 1
Else
PaddlerTotal = PaddlerTotal + 1
End If
ActiveCell.Offset(1, 0).Select
Loop
Select Case currentAge
Case Is >= 60
Cells(k, "J").Value = "Great Grand Master"
GreatGrandMasterCount = GreatGrandMasterCount + 1
Case Is >= 50
Cells(k, "J").Value = "Grand Master"
GrandMastersCount = GrandMastersCount + 1
Case Is >= 40
Cells(k, "J").Value = "Master"
MasterCount = MasterCount + 1
Case Is >= 18
Cells(k, "J").Value = "Senior"
SeniorCount = SeniorCount + 1
Case Is < 18
Cells(k, "J").Value = "Junior"
JuniorCount = JuniorCount + 1
End Select
Range("Paddlers") = PaddlerTotal
Range("Sweeps") = SweepsTotal
Range("Drummers") = DrummersTotal
Range("Juniors").Value = JuniorCount
Range("Seniors").Value = SeniorCount
Range("Masters").Value = MasterCount
Range("GrandMasters").Value = GrandMastersCount
Range("GreatGrandMaster").Value = GreatGrandMasterCount
'Set totals on Summary sheet
Worksheets("Summary").Activate
'Move cursor to A1 cell so data shows on Summary sheet without scrolling
Range("A1").Select
Range("Total").Value = JuniorCount + SeniorCount + MasterCount + GrandMastersCount + GreatGrandMasterCount
Range("RoleTotal").Value = PaddlerTotal + SweepsTotal + DrummersTotal
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End With
End Sub
Display More
Full code. Any help is much appreciated.
Code
Sub mcrValidate()
Dim LastRow As Long
Dim k As Long
Dim currentAge As Integer
Dim DrummersTotal As Integer
Dim SweepsTotal As Integer
Dim PaddlerTotal As Integer
Dim JuniorCount As Integer
Dim SeniorCount As Intege
Dim MasterCount As Integer
Dim GrandMastersCount As Integer
Dim GreatGrandMasterCount As Integer
'Macro to do te following things:
'Add headings to the Combined sheet
'Standardise all the fonts to Arial 12pt
'Add Category column
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
''' Set the sheet font format
With Sheets("Combined").Cells.Font
.Name = "Arial"
.Italic = False
.Bold = False
.Size = 12
.ColorIndex = 1
End With
Sheets("Combined").Select
With Sheets("Combined")
''' Set the table Headers
.Range("A1:J1").Value = Array("Last Name", "First Name", "Gender", "Date of Birth", "Address", "Suburb", "State", "Postcode", "Role", "Category")
LastRow = Cells(Rows.Count, "D").End(xlUp).Row ''' Last row with data in DOB column (D)
For k = 2 To LastRow
''' Get the current age
currentAge = DateDiff("yyyy", .Cells(k, "D").Value, Date)
'' Assign Categories as per person current age
Select Case currentAge
Case Is >= 60
Cells(k, "J").Value = "Great Grand Master"
Case Is >= 50
Cells(k, "J").Value = "Grand Master"
Case Is >= 40
Cells(k, "J").Value = "Master"
Case Is >= 18
Cells(k, "J").Value = "Senior"
Case Is < 18
Cells(k, "J").Value = "Junior"
End Select
Next k
With .Range("R1:T1")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.ReadingOrder = xlContext
End With
Do While ActiveCell <> ""
If ActiveCell.Value = "D" Then
DrummersTotal = DrummersTotal + 1
ElseIf ActiveCell.Value = "S" Then
SweepsTotal = SweepsTotal + 1
Else
PaddlerTotal = PaddlerTotal + 1
End If
ActiveCell.Offset(1, 0).Select
Loop
Select Case currentAge
Case Is >= 60
Cells(k, "J").Value = "Great Grand Master"
GreatGrandMasterCount = GreatGrandMasterCount + 1
Case Is >= 50
Cells(k, "J").Value = "Grand Master"
GrandMastersCount = GrandMastersCount + 1
Case Is >= 40
Cells(k, "J").Value = "Master"
MasterCount = MasterCount + 1
Case Is >= 18
Cells(k, "J").Value = "Senior"
SeniorCount = SeniorCount + 1
Case Is < 18
Cells(k, "J").Value = "Junior"
JuniorCount = JuniorCount + 1
End Select
Range("Paddlers") = PaddlerTotal
Range("Sweeps") = SweepsTotal
Range("Drummers") = DrummersTotal
Range("Juniors").Value = JuniorCount
Range("Seniors").Value = SeniorCount
Range("Masters").Value = MasterCount
Range("GrandMasters").Value = GrandMastersCount
Range("GreatGrandMaster").Value = GreatGrandMasterCount
'Set totals on Summary sheet
Worksheets("Summary").Activate
'Move cursor to A1 cell so data shows on Summary sheet without scrolling
Range("A1").Select
Range("Total").Value = JuniorCount + SeniorCount + MasterCount + GrandMastersCount + GreatGrandMasterCount
Range("RoleTotal").Value = PaddlerTotal + SweepsTotal + DrummersTotal
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End With
End Sub
Display More