Search list, storing and returning values based on results

[motoflyboy]

Hi
I sort of have the some small peices of this code worked out but (as a relative newb) nothing that works.I am out of my depth on this one!
Can someone point me in the right direction?
I have a table of data which i need to search through (approx 20000 rows).
Column C contains a 'ProductCode' and column D contains a 'Rate'
I need to provide a value in column E which represents 'status'
Code needs to:
Look down Column D to find when the value of 'Rate' drops below 100.
If Column D value is < 100, look down column C to check if 'ProductCode' changes before 'Rate" becomes >100.
When 'ProductCode' does change, I need the value of E(Status) to change for each row where D(Rate) was <100.

I hope i have explained this OK.
One of the major blocks for me is how to get the code to remember which was the first row where columnD was < than 100, then going BACK UP the table to find that row and change the values of columnE IF it does find that column C changes before column D becomes>100.

 

[motoflyboy]

Thanks for not giving up on me!

What if i explain it like this:
Looking down column D, find any ranges where D<100
So in our case there are 2 ranges where D<100:

D6:D9 and D14:D17

For each of those ranges look across to column C to see if there is a change:

In the range (C6:C9) does the code change? (NO)
and in the range (C14:C17) does the code change (YES)

So if the answer above is YES (the code DOES change) then make column E for the same range (E14:E17) = 1.

I really appreciate your efforts to understand this question!!!

 

[jindon]

Try the code

Sub test()
Dim a, i As Long, myCode As String, myStatus As Integer
a = Range("a1").CurrentRegion.Resize(,5).Value
For i = 2 To UBound(a,1)
   If a(i,4) > =100 Then
      myCode = a(i,3) : myStatus = 2
      Do While a(i + n, 4) >= 100
         n = n + 1
         If i + n > UBound(a,1) Then Exit Do
      Loop
      For ii = i To i + n : a(ii,5) = myStatus : Next
   Else
      myCode = a(i,3) : myStatus = 1
      Do While a(i + n, 4) < 100
         If a(i + n, 3) <> myCode Then myStatus = 3
         n = n + 1
         If i + n > UBound(a,1) Then Exit Do
      Loop
      For ii = i To ii + n : a(ii,5) = myStatus : Next
   End If
   i = i + n : n = 0
Next
Range("a1").CurrentRegion.Resize(,5).VAlue = a
End Sub

 

[motoflyboy]

Thanks Jindon,
This looks like a really good start!

Currently though the status carries down 1 row too far,
IE: staus of "2" assigned for E2:E6 instead of E2:E5
(Same all the way down - E2 is correct but from then on results are offset by one row)
Also - possibly not your code but weird - date in column A gets formatted tm/dd/yyy/hh:mm .

Thanks very much for you help so far...

 

[jindon]

How about

Sub test()
Dim a, i As Long, myCode As String, myStatus As Integer, b()
a = Range("a1").CurrentRegion.Resize(,4).Value
ReDim b(1 To UBound(a,1) - 1, 1 To 1)
For i = 2 To UBound(a,1)
   If a(i,4) > =100 Then
      myCode = a(i,3) : myStatus = 2
      Do While a(i + n, 4) >= 100
         n = n + 1
         If i + n > UBound(a,1) Then Exit Do
      Loop
      For ii = i To i + n - 1 : b(ii - 1,1) = myStatus : Next
   Else
      myCode = a(i,3) : myStatus = 1
      Do While a(i + n, 4) < 100
         If a(i + n, 3) <> myCode Then myStatus = 3
         n = n + 1
         If i + n > UBound(a,1) Then Exit Do
      Loop
      For ii = i To ii + n - 1 : b(ii-1,1) = myStatus : Next
   End If
   i = i + n - 1 : n = 0
Next
Range("e2").Resize(UBound(b,1)).Value = b
End Sub

 

[motoflyboy]

Thank you very much!!