Checking if data exist in range, if it exist add up the number

[daphnedaphy]

Hello Everyone,

I'm pretty new to VBA and hope you can advise me in some coding which I'm currently stuck for days.

I have 1 sheet (Sheet 2) which have a list of name, designation, department and hours. In another sheet (Sheet 2) I would like to run a summary of the total number hours in each department has. I'm using VBA nested loop to check if data exist in "Sheet 2". If it's exist it will calculated the number of hours in each department.

The issue i'm facing is, the loop only run once. I tried writing the pseudo code, the logic work but when I run it. The code run once.

Sheet 1 data

Name	Role	Department	Hours Spent
AAA	Designer	Creative	2
BBB	Developer	Development	4
CCC	Senior Developer	Development	6
DDD	Account Manager	Account	8
EEE	Account Assistant	Account	10

<tbody>
</tbody>

Sheet 2 data (output)

Department	Total Hours
Account	18
Creative
Development

<tbody>
</tbody>

My Code as follow:

Sub CountResourceHours()
    Dim rTimeHr, rDashboardTime As Range
    
    Set rTimeHr = Worksheets("Sheet2").Range("C2")
    Set rDashboardTime = Worksheets("Sheet3").Range("A2")
    
    Do While rDashboardTime.Text <> ""
        
        Do While rTimeHr.Text <> ""
            If rTimeHr = rDashboardTime Then
                rDashboardTime.Offset(columnoffset:=1) = rDashboardTime.Offset(columnoffset:=1) + rTimeHr.Offset(columnoffset:=1)
            End If
            
            Set rTimeHr = rTimeHr.Offset(rowoffset:=1)
            
        Loop
        
    Set rDashboardTime = rDashboardTime.Offset(rowoffset:=1)
        
    Loop
End Sub

Can someone shine me some light?
Million Thanks!

 

[Joe4]

Welcome to the Board!

Why do you need VBA at all? It seems you should be able to do it with the SUMIF formula.
See Excel's help files on the SUMIF function for details and examples.

 

[VBA Geek]

you can make it very simple by using a SUMIF formula, or you need absolutely VBA?

 

[healey21]

Welcome to the forum without VBA using a SUMIF the following works.

Excel Workbook
	A	B
1	Department	Totals
2	Creative	2
3	Development	10
4	Account	18
Sheet2

 

[daphnedaphy]

Thank you for welcoming me to the board and thanks for the proposed solutions! Yes, unfortunately I have to use VBA as this is part of a dashboard I'm working on. The item list shown in Sheet 2 will be undefined, depending on how much data the user would like to input (e.g. it might be just 10 rows, 20 rows or 50 rows). Depending on the data I have collected I would need to calculate the total number of hours per department and from there, I will be generating a piechart (done) which will be showing in the dashboard for presentation purposes.

Can you guys shine more light to help me please?
Millions Thanks!

 

[healey21]

SUMIF in code to do the same is shown below:

Sub SumRangeOnCriteria()
Dim Sumact1 As Long, Sumact2 As Long, Sumact3 As Long
Sumact1 = Application.SumIf(Sheets("Sheet1").Range("C2:C10"), "Creative", Range("D2:D10"))
Sheets("Sheet3").Range("B2").Value = Sumact1
Sumact2 = Application.SumIf(Sheets("Sheet1").Range("C2:C10"), "Development", Range("D2:D10"))
Sheets("Sheet3").Range("B3").Value = Sumact2
Sumact3 = Application.SumIf(Sheets("Sheet1").Range("C2:C10"), "Account", Range("D2:D10"))
Sheets("Sheet3").Range("B4").Value = Sumact3
End Sub

 

[daphnedaphy]

Thanks for the propose solution! This work too, but the downside is where if the user had 100 rows of data I would need to change the code manually. Do you think if I use a switch case will work too?

Thanks!