Loop Through Range And Check Value In Column

[mouse88]

This may be easy to do but my minds on overtime today.

Heres the code I have so far:

'Look Up Name, Find And Return Total Of Selected Option'
Public Function GetShrinkage(Name As String, Activity As String, Report As Range) As Date
'-----------------'
'Declare Variables'
'-----------------'
Dim StartRowIndex As Long 'To Store The First Row Number'
Dim EndRowIndex As Long 'To Store The Last Row Number'
Dim ReturnValue As Date 'To Hold The Running Total'
Dim TestRange As Range 'To Hold The Resulting Range Of The Search'
'-------------'
'Set Variables'
'-------------'
ReturnValue = TimeValue("00:00:00") 'Set Starting Value So 00:00:00 Is Returned For No Match'
StartRowIndex = 0 'Set Initial Value'
EndRowIndex = 0 'Set Initial Value'
'--------------------'
'Get Start Row Number'
'--------------------'
Set TestRange = Report.Find(Name) 'Look For Name And Store Resulting Range'
'Test If Name Found'
If TestRange Is Nothing Then
    GetShrinkage = CVErr(xlErrNA)
    Exit Sub
End If
'Result Found/Store Start Row Index'
StartRowIndex = TestRange.Row
'--------------------'
'Get End Row Number'
'--------------------'
Dim LoopCounter As Long 'To Loop Through Rows'
LoopCounter = StartRowIndex 'Set Start Row Value'

    
End Function

Where I have the heading "Get Row End Number" I want to loop through column A of the range 'Report' that is passed as a parameter starting with the row number that is stored in 'StartRowIndex'. I want the loop to stop when the first part of the cells value = "Agent"

Left(Cell.Text, 5) = "Agent"

And then store the cells row number in EndRowIndex.

The only thing im not sure about is looping through column A in a range that has already been specified.

 

[JackDanIce]

With my changes in red, try:

'Look Up Name, Find And Return Total Of Selected Option'
Public Function GetShrinkage(Name As String, Activity As String, Report As Range) As Date
'-----------------'
'Declare Variables'
'-----------------'
Dim StartRowIndex As Long 'To Store The First Row Number'
Dim EndRowIndex As Long 'To Store The Last Row Number'
Dim ReturnValue As Date 'To Hold The Running Total'
Dim TestRange As Range 'To Hold The Resulting Range Of The Search'
'-------------'
'Set Variables'
'-------------'
ReturnValue = TimeValue("00:00:00") 'Set Starting Value So 00:00:00 Is Returned For No Match'
StartRowIndex = 0 'Set Initial Value'
EndRowIndex = 0 'Set Initial Value'
'--------------------'
'Get Start Row Number'
'--------------------'
Set TestRange = Report.Find(Name) 'Look For Name And Store Resulting Range'
'Test If Name Found'
If TestRange Is Nothing Then
    GetShrinkage = CVErr(xlErrNA)
    Exit Sub
End If
'Result Found/Store Start Row Index'
StartRowIndex = TestRange.Row
'--------------------'
'Get End Row Number'
'--------------------'
Dim LoopCounter As Long 'To Loop Through Rows'
LoopCounter = StartRowIndex 'Set Start Row Value'

Dim cell As Range
For Each cell In TestRange
    If UCase(Left(cell.Value, 5)) = "AGENT" Then
        EndRowIndex = cell.Row
        Exit For
    End If
Next cell

End Function

An observation, personally, I found the above code over saturated with comments and explanations for what each part does, a bit OTT. Completely understand (and I do so myself) the need to comment code for other readers but there's a balance between too much that itself makes the code difficult to read/follow and too little that doesn't aid either. Just an opinion though.

 

[Norie]

Couldn't you just use Find again to find the first row with a value beginning with your search term?

 

[mouse88]

How can I get it to only loop through column A of the range? The range thats passed to the function will always be ("A:Z").

I thought about using the find function but there will be over 250 rows that actually contain "Agent" and I need to specifically find the next one down after the StartRowIndex.

Thanks

 

[JackDanIce]

Change my bit in red to:

Dim i as Long
For i = 1 to Range("A" & Rows.Count).End(xlUp).Row
    If UCase(Left(Range("A" & i), 5)) = "AGENT" Then
        EndRowIndex = i
        Exit For
    End If
Next cell

 

[mouse88]

I managed to get it working with this:

Dim LoopCounter As Long
LoopCounter = StartRowIndex + 1
 
'Loop Rows Until Next Name Found'
Do Until Left(Report.Range("A" & LoopCounter).Text, 5) = "Agent"
    LoopCounter = LoopCounter + 1
Loop
 
EndRowIndex = LoopCounter 'Set End Row Index'

The problem I have now is when im doing the find on the name, if that particular name is the last one in the report it will never find another row containing "Agent" so it returns an error.

Any ideas how I can tell it is the last name on the report?

Report:

 

[JackDanIce]

The For Loop I suggested wouldn't allow you to go past the last row that contained data