Trying to Exit a Loop when VLookup no longer returns a valid value

[Nat1281]

Hi All, I am new to posting, so if I need to provide more details, please do not hesitate to let me know. Thank you in advance for any assistance you can provide!

I have a column with a Status in it ("Awarded", "Lost", and "Pending" are the options).
I am trying to look down that column and when I find an "Awarded" entry, I want to return the ID2 which is a few columns over.
After returning ID2, I want to Loop back and look for the next "Awarded" in the column, and again return the next ID2. (Each Line item has a unique ID2)
I want to keep doing this until there are no more entries that read "Awarded".

I am able do this with the following code; however, after the very last "Awarded" is found, when it goes through the loop again, it produces an error. I can only assume the error is due to the vlookup not being able to find another "Awarded" entry within the column. I have tried to stop the loop in several different ways, but I can't seem to figure it out.

The following is the code:

Sub Loop2()

'Define all variables
Dim RowID As Integer
Dim ID2 As String
Dim sheet As Worksheet
Dim i As Integer

'Set i to be 4 to begin returns on 4th row
i = 4
RowID = 4
ID2 = 1

'select first line of data in the Open Pipeline
Application.Goto (ActiveWorkbook.Sheets("Open Pipeline").Range("Z4"))


'Set Loop to stop when empty cell is reached

Do Until i = 100

Set sheet = ActiveWorkbook.Sheets("Open Pipeline")

'look for "Awarded" in the Open Pipeline sheet and return the ID number in column AE
ID2 = Application.Vlookup("Awarded", sheet.Range(Cells(RowID, 26), Cells(200, 29)), 4, False)
sheet.Cells(i, 31).Value = ID2

RowID = Application.Vlookup(sheet.Cells(i, 31), sheet.Range(Cells(i, 29), Cells(200, 30)), 2, False)
sheet.Cells(i, 32).Value = RowID

RowID = RowID + 1
sheet.Cells(i, 33).Value = RowID

i = i + 1

Loop

End Sub



The error I am receiving states: Run Time Error; Type Mismatch
When I debug, it goes to the following line of code:
ID2 = Application.Vlookup("Awarded", sheet.Range(Cells(RowID, 26), Cells(200, 29)), 4, False)

 

[Snakehips]

Nat1281,

Welcome to MrExcel.

You could try ....

Sub Loop2()
'Define all variables
Dim RowID As Integer
Dim ID2 As String
Dim sheet As Worksheet
Dim i As Integer
On Error GoTo Done
'Set i to be 4 to begin returns on 4th row
i = 4
RowID = 4
ID2 = 1
'select first line of data in the Open Pipeline
Application.Goto (ActiveWorkbook.Sheets("Open Pipeline").Range("Z4"))

'Set Loop to stop when empty cell is reached
Do Until i = 100
Set sheet = ActiveWorkbook.Sheets("Open Pipeline")
'look for "Awarded" in the Open Pipeline sheet and return the ID number in column AE
ID2 = Application.VLookup("Awarded", sheet.Range(Cells(RowID, 26), Cells(200, 29)), 4, False)
sheet.Cells(i, 31).Value = ID2
RowID = Application.VLookup(sheet.Cells(i, 31), sheet.Range(Cells(i, 29), Cells(200, 30)), 2, False)
sheet.Cells(i, 32).Value = RowID
RowID = RowID + 1
sheet.Cells(i, 33).Value = RowID
i = i + 1
Loop
Done:
On Error GoTo 0
End Sub

Hope that helps.

 

[Nat1281]

Thank you so much, that was a perfect fix. (I had been trying similar options but had them within the Loop, never outside of it) You have saved me countless hours!