Searching and calculating more than 1 record

[newtoexcel123]

I'm trying to create a small macro to search for records and add up the total number of hours each spend. I've hard code the search criteria. For instance, I want the marco to count the number of record(s) that are associated with Alan, and number of record(s) that are associated with Steven etc....

This is how the data in excel workbook looks like:

Name Hour Work Hour Rest
Alan 3 1
Alan 5 0.5
Steven 2 0
Alan 3 1.5
Ricky 5 0.5
Steven 3 0

I want the output to be like this:

Name Hour Work Hour Rest
Alan 11 3
Steven 5 0
Ricky 5 0.5

This is the search
Private Sub SearchRecord()
Sheets("Sheet1").Select
Sheets("Sheet1").Range("A1:D22").Select

Set FoundText = Selection.Find(What:="Alan", After:=ActiveCell, LookIn _
:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)

If Not FoundText Is Nothing Then
FoundText.Select
FoundCellRow = ActiveCell.Row
Else
End If

Set FoundText = Selection.Find(What:="Steven", After:=ActiveCell, LookIn _:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _xlNext, MatchCase:=False, SearchFormat:=False)

If Not FoundText Is Nothing Then
FoundText.Select
FoundCellRow = ActiveCell.Row
Else

End If


End Sub

Using the above code, I could find 1 record only. How to code in such a way that I'm able to find all the record(s) associated with the search criteria.

Thank you.

Yours sincerely,
Johnson

 

[Makrini]

why use a macro?

=SUMIF(A1:A1000,"Alan",B1:B1000)

 

[newtoexcel123]

Hi, thank you for your message. The reason why I use marco is because this is a repetition tasks, and there are hundred of files. Therefore, I'm thinking of writing a marco to do this task.