Still stuck on finding the last occurence...thanks...

[ste mac]

Hi, the code below is as far as I have got (courtesy of this ng). I
asked a question similar
to this a few days ago, and with help have arrived to this point.

By testing it with msg box's it seems to be doing what it is supposed
to for the most part.
It is supposed to find the last occurence in the range on sheet
'BaseData' of a value =>
minValue and <=maxValue, and pass the row number the value was found on
to another
sheet named 'Main'. Thats it, all that is required is the row number of
the last occurence.

I am almost sure the code is stepping backwards through the range on
sheet 'BaseData' but
does not seem to start stepping backwards from 'LastRow' but from row
6!...gggaaaaaarrrrhhhhh.....

Can anybody see what I've done wrong? Have I gone the wrong way about
it?

cheers

ste

Sub aaa()
Application.ScreenUpdating = False

Dim DataRng As Range
Dim r As Integer
Dim LastRow As Long
Dim minValue As Long
Dim maxValue As Long

minValue = Sheets("Main").Range("I" & Rows.Count).End(xlUp).Value
maxValue = Sheets("Main").Range("J" & Rows.Count).End(xlUp).Value

LastRow = Sheets("BaseData").Cells(Rows.Count, "B").End(xlUp).Row

Set DataRng = Sheets("BaseData").Range("B2:U" & LastRow)

For r = LastRow To 2 Step -1

If DataRng(r) >= minValue And DataRng(r) <= maxValue Then
Sheets("Main").Range("L65536").End(xlUp).Offset(1, 0).Value
= r
End If

Next r

Application.ScreenUpdating = True
End Sub

 

[Gary''s Student]

Since you are moving upwards from LastRow, you need to stop as soon as you
have found something.

If DataRng(r) >= minValue And DataRng(r) <= maxValue Then
Sheets("Main").Range("L65536").End(xlUp).Offset(1, 0).Value= r
Exit For
End If

 

[ste mac]

Hi Gary's Student, thanks for the input and I have adjusted the code as
you suggest, but
it still returns the wrong row number, by manually checking the last
occurence of a number
=>minValue and <=maxValue was 14 rows ago. (Row 84)

The code is returning row 6! there isn't even a valid value on row 6,
and I have
no idea why. I just can't see whats wrong...

thanks for your help

ste

 

[Bob Phillips]

Why are you stepping back through the rows?

I think the problem is caused by the cells you are testing. You are stepping
up through rows, but DataRng(r) will step through the columns in row 2 up to
U2, then back to row 3 etc.

What should the min and max values be tested against?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Gary''s Student]

Let's try going forward (cell-by-cell). Because we are going forward, we
don't need the Exit statememt:

Set DataRng = Sheets("BaseData").Range("B2:U" & LastRow)


For Each rr In DataRng
If rr.Value >= minValue And rr.Value <= maxValue Then
Sheets("Main").Range("L65536").End(xlUp).Offset(1, 0).Value = rr.Row
End If
Next rr


Application.ScreenUpdating = True



Moving forward like this means we may be recording the row several times,
but the last guy will win!

 

[ste mac]

Hi Bob, I do not fully understand what the code is actually doing to
the depth
that you do, I thought the code was just stepping back up the rows in
columns
"B" thru "U" searching for the first cell it comes across with a value
in it that fits
the criteria '>= minValue And <= maxValue '

I was trying to step back through the rows because I only need the row
number
of the last occurence of a cell value that fits '>= minValue And <=
maxValue '

The code I have is pieced together from all over this n.g with any
snippets of advice
I could find. I have obviously got it wrong, is there an easier way?

cheers

ste

 

[Bob Phillips]

I was going to say that I thought Gary's revised code does what you want,
but it doesn't work backwards as you said, so try this

Sub aaa()
Application.ScreenUpdating = False

Dim DataRng As Range
Dim r As Integer
Dim LastRow As Long
Dim minValue As Long
Dim maxValue As Long

minValue = Sheets("Main").Range("I" & Rows.Count).End(xlUp).Value
maxValue = Sheets("Main").Range("J" & Rows.Count).End(xlUp).Value

LastRow = Sheets("BaseData").Cells(Rows.Count, "B").End(xlUp).row

Set DataRng = Sheets("BaseData").Range("B2:U" & LastRow)

For r = LastRow To 2 Step -1

For c = 21 To step - 1
If .Cells(r, c).Value >= minValue And _
.Cells(r, c).Value <= maxValue Then
Sheets("Main").Range("L" & Rows.Count).End(xlUp). _
Offset(1, 0).Value = .Cells(r, c).Value
End If
Next c

Next r

End Sub

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[ste mac]

Gary''s Student, ACE! this is definately one way of doing it.

I can cobble something together to get the last row number, but without
good guys
like you and Bob, I would have been going nowhere fast...

Thanks again.

ste

 

[ste mac]

Bob, Thankyou! now I have two proper avenues to go down...

Thanks to you two guys...

cheers

ste