Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still stuck on finding the last occurence...thanks...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still stuck on finding the last occurence...thanks...
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 -- Gary's Student "ste mac" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still stuck on finding the last occurence...thanks...
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 Gary''s Student wrote: 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 -- Gary's Student "ste mac" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still stuck on finding the last occurence...thanks...
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) "ste mac" wrote in message oups.com... 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 Gary''s Student wrote: 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 -- Gary's Student "ste mac" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still stuck on finding the last occurence...thanks...
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! -- Gary's Student "ste mac" wrote: 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 Gary''s Student wrote: 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 -- Gary's Student "ste mac" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still stuck on finding the last occurence...thanks...
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 wrote: 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) "ste mac" wrote in message oups.com... 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 Gary''s Student wrote: 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 -- Gary's Student "ste mac" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still stuck on finding the last occurence...thanks...
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" wrote in message ups.com... 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 wrote: 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) "ste mac" wrote in message oups.com... 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 Gary''s Student wrote: 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 -- Gary's Student "ste mac" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still stuck on finding the last occurence...thanks...
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still stuck on finding the last occurence...thanks...
Bob, Thankyou! now I have two proper avenues to go down...
Thanks to you two guys... cheers ste |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding last occurence of Interior.ColorIndex 36 | New Users to Excel | |||
Finding first occurence of a number | Excel Worksheet Functions | |||
Finding how many events since last occurence in a database list | Excel Worksheet Functions | |||
Finding most common occurence of values in cells containing letters and numbers | Excel Worksheet Functions | |||
Occurence #'s | Excel Discussion (Misc queries) |