Checking Value In Multiple Rows

[vahnx]

I have a spreadsheet with many rows and columns, but column E contains a unique identifier/flag (DUP, HLF, or QTR) that I can compare with. When it has this flag I must check all the rows below it for those same flags. Could I say have an array with these 3 flags, and say "If one flag is found, just scan down until flag(s) have ended".

The data there is edited out, but you see all the DUP's, I need exactly the information one row above it all the way to the last DUP in that case. I know how to do it but cannot implement it (brain = hurt).

loop scan through sheet
pick up flag
index -= 1
for loop start
data collection
exit for when next row is no longer a flag

Hope this makes sense to you all. Thanks in advance.

 

[mirabeau]

Would you be able to post a somewhat clearer example of just what you want?

 

[vahnx]

OK can you see what I'm trying to get at with the following code.

Dim index, index2 as Integer
sindex = 0 ' start
eindex = 0 ' end

For i = 1 to 22
   If Range("B" & CInt(i)).Value = "DUP" Then ' first occurance line 3
       sindex =  i - 1                                   ' need value from one line before
       eindex = i                                         ' needs to be the last DUP in a row
       if Range("B" & CInt(i)).Value = "DUP" then  ' if line has dup then increase index
               eindex = eindex + 1
               if Range("B" & CInt(i + 1)).Value = "DUP" then ' if next line doesnt have dup then increase index again
                              eindex = eindex + 1
                         if Range("B" & CInt(i + 2)).Value = "DUP" then ' if next line doesnt have dup then increase index again
                              eindex = eindex + 1

               end if
        end if
    end if

   for i = sindex to eindex
       ' collect data
   next i

next i

I need something to that affect, like a Do Until Loop maybe, "Do until column B is blank". Recursive checking until a blank space is found, then gathering the data from that starting index (black arrow) to ending index (blue arrow) and I can do the rest from there.

Still to confusing? :S