Find Last Value
 

I have many entries describing sales events, and one of the columns in every
entry is quarter. I'd like to find where one quarter starts and where one
ends without doing any filtering or deleting.
For example, i have

___K___
1|Q1
2|Q1
3|Q1
4|Q1
5|Q1
6|Q2
7|Q2

I'd like to use a code that, in this example, would return row 5 as the last
Q1 value. Is this possible?
thanks in advance

comparini3000

Find Last Value
 

Simple enough

=MAX(IF(A1:A20="Q1",ROW(A1:A20)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

To get the first row, change MAX to MIN

--
HTH

Bob Phillips

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

"comparini3000" wrote in message
...
I have many entries describing sales events, and one of the columns in
every
entry is quarter. I'd like to find where one quarter starts and where one
ends without doing any filtering or deleting.
For example, i have

___K___
1|Q1
2|Q1
3|Q1
4|Q1
5|Q1
6|Q2
7|Q2

I'd like to use a code that, in this example, would return row 5 as the
last
Q1 value. Is this possible?
thanks in advance

comparini3000

Find Last Value
 

Give this a try...

Public Sub FindLast()
Dim rngFound As Range
Dim rngToSearch As Range

Set rngToSearch = Range("K2", Cells(Rows.Count, "K").End(xlUp))
Set rngFound = rngToSearch.Find(What:="Q1", _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchDirection:=xlPrevious)
If rngFound Is Nothing Then
MsgBox "Sorry... Not Found"
Else
MsgBox "Found on row " & rngFound.Row
End If

End Sub

--
HTH...

Jim Thomlinson


"comparini3000" wrote:

I have many entries describing sales events, and one of the columns in every
entry is quarter. I'd like to find where one quarter starts and where one
ends without doing any filtering or deleting.
For example, i have

___K___
1|Q1
2|Q1
3|Q1
4|Q1
5|Q1
6|Q2
7|Q2

I'd like to use a code that, in this example, would return row 5 as the last
Q1 value. Is this possible?
thanks in advance

comparini3000

Find Last Value
 

Mr. Phillips, thank you for your quick reply, the formula worked wonderfully.
However, I was looking for a VBA solution. How would I do that?

"Bob Phillips" wrote:

Simple enough

=MAX(IF(A1:A20="Q1",ROW(A1:A20)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

To get the first row, change MAX to MIN

--
HTH

Bob Phillips

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

Find Last Value
 

That's excellent, it's exactly what I needed. Thank you for your help!

"Jim Thomlinson" wrote:

Give this a try...

Public Sub FindLast()
Dim rngFound As Range
Dim rngToSearch As Range

Set rngToSearch = Range("K2", Cells(Rows.Count, "K").End(xlUp))
Set rngFound = rngToSearch.Find(What:="Q1", _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchDirection:=xlPrevious)
If rngFound Is Nothing Then
MsgBox "Sorry... Not Found"
Else
MsgBox "Found on row " & rngFound.Row
End If

End Sub

--
HTH...

Jim Thomlinson


"comparini3000" wrote:

I have many entries describing sales events, and one of the columns in every
entry is quarter. I'd like to find where one quarter starts and where one
ends without doing any filtering or deleting.
For example, i have

___K___
1|Q1
2|Q1
3|Q1
4|Q1
5|Q1
6|Q2
7|Q2

I'd like to use a code that, in this example, would return row 5 as the last
Q1 value. Is this possible?
thanks in advance

comparini3000

Find Last Value
 

MsgBox ActiveSheet.Evaluate("MAX(IF(A1:A20=""Q1"",ROW(A1: A20)))")



--
HTH

Bob Phillips

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

"comparini3000" wrote in message
...
Mr. Phillips, thank you for your quick reply, the formula worked
wonderfully.
However, I was looking for a VBA solution. How would I do that?

"Bob Phillips" wrote:

Simple enough

=MAX(IF(A1:A20="Q1",ROW(A1:A20)))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.

To get the first row, change MAX to MIN

--
HTH

Bob Phillips

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