Searcing throught using part of string

[bundyloco]

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).Value, 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.

[Jim Cone]

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"
<[email protected]>
wrote in message
news:[email protected]

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).Value, 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

[bundyloco]

Thanks for the reply. I tried the code, but it it only gives me rngFind=1000 on the message box. I'll keep working with it. Also, what is the else statement doing? I'm still learning VBA and am at the point where I'm more dangerous than effective.

[Jim Cone]

b.

The code is not finding the text before it gets to row 1000.
The 1000 is an arbitrary limit. It just prevents the code from
going thru all 65000 rows. Change it to whatever is appropriate.
Also make sure the search text is correct. It looks at the first
8 characters in the cell to see if they match exactly to "Run Rate".

The Else statement moves the cell down one row each time the
text is not found. It keeps doing that until the "limit" is reached.

Jim Cone
San Francisco, USA


"bundyloco"
<[email protected]>
wrote in message
news:[email protected]

Thanks for the reply. I tried the code, but it it only gives me
rngFind=1000 on the message box. I'll keep working with it. Also, what
is the else statement doing? I'm still learning VBA and am at the point
where I'm more dangerous than effective.
--
bundyloco