NEED with either fixing formual or preferably a macro to do this procedure. Please!

[estatefinds]

I have data that I would like to be counted from last number entered. meaning the macro would count from this last number entered up the data until it see's a match and returns the count of rows between these two matches. so for example in the file attached the 1279 was enetered and the the next match from the last enetered to its match is 55 rows. and this would be placed in column X to the right.

the current formula now counts from the top and the last entered number and is in accurate. the formula is found in cell W and X 20 and Ak and AL 20.

Ignore the data in the in the columns N:T this was Me checking why my current way wasnt accurate.

but if you look in the column S where the "1" and the "55" is in red this is the accuare count from newest number in column U 484 and matched the most recent match in U 430. the number 1279. so the 55 would be placed in column X at 484.

I prefer a macro (cause it is cleaner for my worksheet) in which I select all the data in column U and it will start with the newest number 1279 and return count of rows to its next match 1279 which will be returned as 55 in column X to the right. then it will read the next number up 2450 and the count would be 206. and so on.

Also need to work with same concept with data starting in column AI.

if you have any questions for clarification please ask thank you!

I attached a file.
I thank you in Advance for the help on this.

Sincerely, Dennis

[p45cal]

In X484 a formula:
=ROW()-MAX(IF($U$1:$U483=U484,ROW($U$1:$U483)))
array-entered.
Copy up/down.

For the formula to give the correct value the values it's looking at must be all numbers or all text but not a mixture as you have now.

[SamT]

Select any cell in the desired column and run the macro

Option Explicit


Sub MacroToCallFromToolsMenu()

Dim StartFromBottom As Range
Dim FoundPrevious As Range

Set StartFromBottom = ActiveSheet.Cells(Rows.Count, Selection.Column).End(xlUp)
Set FoundPrevious = StartFromBottom.EntireColumn.Find(What:=StartFromBottom, SearchDirection:=xlPrevious, _
After:=StartFromBottom)

If FoundPrevious Is Nothing Then Set FoundPrevious = StartFromBottom.EntireColumn.Find _
(What:=CStr(StartFromBottom), SearchDirection:=xlPrevious, After:=StartFromBottom)

If FoundPrevious Is Nothing Then
  MsgBox "Did not find a previous entry for " & StartFromBottom
  Exit Sub
End If

StartFromBottom.Offset(0, 3) = StartFromBottom.Row - FoundPrevious.Row



End Sub

Doubleclick any cell in columns U or AI and the macro will run

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Intersect(Target, Range("U:U")) Is Nothing And Intersect(Target, Range("AI:AI")) Is Nothing Then Exit Sub

Cancel = True

Dim StartFromBottom As Range
Dim FoundPrevious As Range

Set StartFromBottom = ActiveSheet.Cells(Rows.Count, Target.Column).End(xlUp)
Set FoundPrevious = StartFromBottom.EntireColumn.Find(What:=StartFromBottom, SearchDirection:=xlPrevious, _
After:=StartFromBottom)

If FoundPrevious Is Nothing Then Set FoundPrevious = StartFromBottom.EntireColumn.Find _
(What:=CStr(StartFromBottom), SearchDirection:=xlPrevious, After:=StartFromBottom)

If FoundPrevious Is Nothing Then
  MsgBox "Did not find a previous entry for " & StartFromBottom
  Exit Sub
End If

StartFromBottom.Offset(0, 3) = StartFromBottom.Row - FoundPrevious.Row

End Sub

Thanks, p45cal for that notice about numbers as text.

[SamT]

bump to OP. I edited my previous

[estatefinds]

Ok thank you for your help

[estatefinds]

ok I added the macros an when i ran them I got the message "Compile error:
Named arguement not found and on both macros the "direction:=" was highlighted in blue.

Ill send the file with all of the other data removed from W and X, and AK and AL as the macro should be placing these in there.

[estatefinds]

to message above in 2 wouldnt it just look for the matching data in the column AI and if it sees another match just count the rows from most recent to the next matching that is found searching up and return that matching data to the right?

[estatefinds]

oh you mean in ragrds to the format of the data. I had changed it to all text so what writeen or entered it will be what i enter. if I change to number it will put decimal points in there.


ok I
changed it to general the format for the data from text.

[SamT]

I got the message "Compile error:
Named arguement not found and on both macros the "direction:=" was highlighted in blue.

I edited that as noted in my #4. it should be SearchDirection:=

[SamT]

Have you tried my edited versions? Numbers or text won't matter to them, they handle both.

[estatefinds]

ok I added updated macros it works it possible it saves the data so say when i start at the top and run macro it displays result s to the right as it does. then when i go down to the next number and run macro it displays result so that way all the past data remains and new data is added below that? is this possible? like iy looked in original file?

[estatefinds]

yes it works thank you.
it works is it possible it saves the data so say;

when i start at the top and run macro it displays result s to the right as it does. then when i go down to the next number and run macro it displays result and it stays there so that way all the past data remains and new data is added below that as i go. is this possible? like it looked in original file?

so what ever number I click on it run the macro does the search and returns how many rows up the match is to the right of of the date that that i did search on.

also when i ran it i kept returning 54 for the few numbers on ran the macro on.

I attached file to show what Im looking for, this should help.

Thank you

[SamT]

Yes. Both of my macros only work on the new number at the bottom.

Just as you asked for.

You already have all the rest, Why should the macro do those again?

[estatefinds]

I assumed with the file I sent that you understood it to be showing all data and instead of the formula I had originally in place it would be a macro doing this from the bottom up starting at the bottom number. Sorry for the confusion. I'll post on new questions.
Thank you very much for your help!
i appreciate it

[SamT]

Sub MacroToCallFromToolsMenu()
     
    Dim StartFromBottom As Range
    Dim FoundPrevious As Range
     
    Set StartFromBottom = ActiveSheet.Cells(Rows.Count, Selection.Column).End(xlUp)
    
    Do While StartFromBottom.Row > 1 And StartFromBottom <> ""
    Set FoundPrevious = StartFromBottom.EntireColumn.Find(What:=StartFromBottom, SearchDirection:=xlPrevious, _
    After:=StartFromBottom)
     
    If FoundPrevious Is Nothing Then Set FoundPrevious = StartFromBottom.EntireColumn.Find _
    (What:=CStr(StartFromBottom), SearchDirection:=xlPrevious, After:=StartFromBottom)
     
   If FoundPrevious.Row >= StartFromBottom.Row Then
      StartFromBottom.Offset(0, 3) = 0
   Else
    StartFromBottom.Offset(0, 3) = StartFromBottom.Row - FoundPrevious.Row
   End If
     
    Set StartFromBottom = StartFromBottom.Offset(-1)
    Loop
     
     
End Sub

[estatefinds]

That works perfectly!!! Thank you Very much!!!
Great work, I really appreciate it!!!