Searching from the bottom up

[jnagel]

Hello,

I have a list which cycles through an order of numbers:
eg.

0
1
2
3
0
1
2
3
etc.

I want to be able to determine the row number of the last 0. Is there a way to search up from the bottom?

Thanks in advance for your help!

 

[T. Valko]

Hello,

I have a list which cycles through an order of numbers:
eg.

0
1
2
3
0
1
2
3
etc.

I want to be able to determine the row number of the last 0. Is there a way to search up from the bottom?

Thanks in advance for your help!

Try this...

Book1
	A	B	C
2	0	_	6
3	1	_	_
4	2	_	_
5	3	_	_
6	0	_	_
7	1	_	_
8	2	_	_
9	3	_	_
Sheet1

Formula entered in C2:

=LOOKUP(2,1/(A2:A9=0),ROW(A2:A9))

Assumes no empty cells within the range.

 

[jnagel]

Thanks for the quick reply!

Sorry, I should have clarified: I am trying to do this in VBA

 

[brettdj]

If your data was in column A you can Find starting bottom up using Find with A1 and xlprevious, ie

hth

Dave

Sub BottomUp()
Dim rng1 As Range
Set rng1 = Columns("A").Find("0", [a1], xlValues, xlWhole, , xlPrevious)
If Not rng1 Is Nothing Then MsgBox rng1.Row
End Sub