Search more than 1 records

[newtoexcel123]

I trying to create a small macro to look up some values that I've hard coded. For instance, I want to find the number of record(s) that are associated with Alan, and the number of record(s) that are associated with Steven. After which I want to calculate the total number of hours they spend individually.

In excel worksheet
Cell A
Name Hours
Alan 10
Alan 2
Steven 5
Robert 7





Private Sub Search()
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

 

[mikerickson]

Have you looked at COUNTIF or SUMIF?

 

[newtoexcel123]

Hi, thank you for your message. I want to write marco because this is a repetition task, and there are hundred of files. Therefore, I'm wondering whether marco could do the job.

 

[mikerickson]

You want to return values.

Excel's native functions are much better for that than any VB routine.

 

[mikerickson]

(double post)