Loop Statement

[anotherphil]

Hello Board:

In column A I have a series of references. In column B I have either "Project" or "Pursuit". I need code to identify all references associated with "Project" and return that reference in column C. This is what I have so far. Any ideas why this doesn't work?

WR Number Type Project/Pursuit
WR# 64 Pursuit
WR# 131 Project
WR# 172 Project
WR# 188 Pursuit
WR# 545 Project

[/code] Range("B2").Select
Dim Check
Check = True
Do
If ActiveCell = "Project" Then
ActiveCell.Offset(0, -1).Select
Dim WR
WR = ActiveCell
Range("C15").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell = WR
Check = False
ElseIf ActiveCell = "Pursuit" Then
ActiveCell.Offset(1, 0).Select
Check = False
Else
Check = True
End If
Loop Until Check = True

 

[Norie]

How doesn't it work?

 

[anotherphil]

It only posts the first reference. In the example above, C2 will be populated with WR# 131. I need WR# 172 and WR# 545 populated as well.

 

[Norie]

phil

Perhaps I'm missing something but couldn't you just use a formula for this?

 

[anotherphil]

Because this is part of a larger code, I cannot use vlookup. That would be nice.

 

[anotherphil]

Because this is part of a larger code, I cannot use vlookup. That would be nice.

 

[erkline]

Not exactly sure what you are looking for but you could try the following.

public sub search()
dim a as range
set a=range("B2")
do until a.value = ""
if a.value = "Project" then
a.offset(0,1).value = true 'or whatever you want in col c
endif
set a = a.offset(1,0)
loop

 

[anotherphil]

I can work with that.

Thanks