how many rows since the last time this happened
 

Row number 1844 column C contains the integer 10.
I want to search back up row C (starting at row 1843) to count how many rows there are between row 1843 and the last time there was a 10 in row C.
I would prefer a solution that uses VBA.

how many rows since the last time this happened
 

Hi,

Am Sun, 17 May 2015 01:33:24 -0700 (PDT) schrieb
:

Row number 1844 column C contains the integer 10.
I want to search back up row C (starting at row 1843) to count how many rows there are between row 1843 and the last time there was a 10 in row C.
I would prefer a solution that uses VBA.

try:

Sub Test()
Dim LRow As Long, i As Long
Dim LVal
Dim c As Range

With ActiveSheet
LRow = .Cells(Rows.Count, 3).End(xlUp).Row
LVal = .Cells(LRow, 3).Value
For i = LRow - 1 To 1 Step -1
If .Cells(i, 3) = LVal Then
MsgBox LRow - i
Exit Sub
End If
Next
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

how many rows since the last time this happened
 

On Sunday, May 17, 2015 at 6:33:28 PM UTC+10, wrote:
Row number 1844 column C contains the integer 10.
I want to search back up row C (starting at row 1843) to count how many rows there are between row 1843 and the last time there was a 10 in row C.
I would prefer a solution that uses VBA.

Hi Claus,
Thank you. It works beautifully.

how many rows since the last time this happened
 

On Sunday, May 17, 2015 at 3:33:28 AM UTC-5, wrote:
Row number 1844 column C contains the integer 10.
I want to search back up row C (starting at row 1843) to count how many rows there are between row 1843 and the last time there was a 10 in row C.
I would prefer a solution that uses VBA.

You may like this better as it fires when you enter a value and will work anywhere in the column
'=============
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tr As Long
Dim MF As Range
tr = Target.Row
If Target.Column < 3 Then Exit Sub
Set MF = Columns(3).Find(What:=Target, After:=Cells(tr, 3), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
MatchCase:=False, SearchFormat:=False)
If Not MF Is Nothing Then
MF.Select
MsgBox "Row " & Abs(MF.Row)
End If
End Sub