b,

There are as many ways to do this as there are people contributing
to this newsgroup. This is just one way and somewhat resembles
your approach...
'----------------------

Sub Testing()
Dim prodCntr As Long
Dim rngFind As Excel.Range
Set rngFind = Cells(1, 1)

Do
If Left$(rngFind, 8) = "Run Rate" Then
Exit Do
Else
Set rngFind = rngFind(2, 1)
End If
Loop Until rngFind.Row = 1000 ' or whatever

prodCntr = rngFind.Row
MsgBox prodCntr
Set rngFind = Nothing
End Sub
'----------------------------

Jim Cone
San Francisco, USA


"bundyloco"

wrote in message


Hello
I want to determine the size of the range of data to loop through
before I use the values to fill an array. Basically, I will start at
row 1 and loop until I find a cell in column A that says "Run Rate data
is from from customer for week" the problem is that I can't use that
entire statment becuase the week number changes from week to week.
But if I could just tell it to loop unti it find the beginning of the
string, I think that would work. Here is what I have, but it's not
working.

prodcntr = 0
For prodcntr = 1 To findrange
Left(Worksheets("Schedule").Cells(prodcntr,1).Valu e, 8) = "Run Rate"))
If Worksheets("Schedule").Cells(prodcntr, 2) < "" And
Worksheets("Schedule").Cells(prodcntr, 2) 0 Then
prodlgnth = prodlgnth + 1
End If
Next prodcntr

ReDim prodarray(6, prodlgnth)

prodlgnth = 0

and from here it goes on to fill and array and compare the values of
the array with values on another worksheet. Any suggestions? I would
like to do something like this to make the program more dynamic,
therefore it would be able to handle new products in the future as they
are added.
--
bundyloco