Find First Occurrence of Value in a Column

[keith0528]

All,

I need some help locating the first occurrence of a number value in a column. The numbers that satisfy the search are 2 thru 10 I'm looking for the 1st occurrence of any of these numbers. Is there a quicker way than doing a loop?

This is a found or not found thing. I'm not interested in listing them if found. As soon as one is found the search is satisfied. then the code moves on to the next bit of business.


I've thrown some code together to give an idea but it's not a cohesive unit.

Set Found6 = Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
       False, SearchFormat:=False)
            If Found6 Is Nothing Then
               
               GoTo Skiddle:


            End If
        
  'give it focus
      'Found6.Select
       Found6.EntireColumn
       Dim L_Row As Integer
       
       L_Row = ActiveSheet.Range("A65536").End(xlUp).Row
       
       Dim BFlag As Boolean
       Dim i As Integer
       BFlag = False
       
       For i = 2 To L_Row
        Do Until Cells.Value > 1 Or Cells.Value <= 10

thanks

 

[BobUmlas]

Assuming the column with the values is A:
Ctrl+Shift+enter:
=MIN(IF(ISNA(MATCH(ROW(2:10),A:A,0)),99999,MATCH(ROW(2:10),A:A,0)))
this will return the row# of the first value between 2 & 10

 

[Rick Rothstein]

I need some help locating the first occurrence of a number value in a column. The numbers that satisfy the search are 2 thru 10 I'm looking for the 1st occurrence of any of these numbers.

Some questions for clarification...

1) You want the code to locate the column with "Total" in it which is also the column you want to search for the values 2 through 10, correct?

2) Will there be other numbers in the cells (e.g., 0, 1, 11, 123, etc.) which are to be ignored or when there is a number, will that number always be 2 through 10?

3) Are the numbers constants (manually typed in) or the calculated result of formulas?

 

[keith0528]

Some questions for clarification...

1) You want the code to locate the column with "Total" in it which is also the column you want to search for the values 2 through 10, correct?

2) Will there be other numbers in the cells (e.g., 0, 1, 11, 123, etc.) which are to be ignored or when there is a number, will that number always be 2 through 10?

3) Are the numbers constants (manually typed in) or the calculated result of formulas?

Hi Rick - #1. no, i've already found and selected the desired column.
#2. yes, there will be other numbers in the column, mostly zero's and ones and yes they should be ignored.
#3. The numbers are derived from a formula

thanks,

 

[Rick Rothstein]

Hi Rick - #1. no, i've already found and selected the desired column.
#2. yes, there will be other numbers in the column, mostly zero's and ones and yes they should be ignored.
#3. The numbers are derived from a formula

Okay, give this code a try...

Sub Test()
  Dim LastRow As Long, Found6 As Range, FirstNumber As Range
[COLOR=#008000]  '
  '  Any previous existing code goes here
  '[/COLOR]
  Set Found6 = Cells.Find("Total", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
  LastRow = Cells(Rows.Count, Found6.Column).End(xlUp).Row
  If Found6 Is Nothing Then GoTo Skiddle
  Set FirstNumber = Cells(Evaluate(Replace("MIN(IF(E2:E#={2,3,4,5,6,7,8,9,10},ROW(E2:E#)))", "#", LastRow)), Found6.Column)
[COLOR=#008000]  '
  '  The FirstNumber range variable is now set to the first cell with a number between 2 and 10
  '
  '  Rest of your code goes here[/COLOR]
  '
 Skiddle:
End Sub

Note that I simplified your search for the Total column and eliminated the unnecessary selecting of cells.

 

[keith0528]

Okay, give this code a try...

Sub Test()
  Dim LastRow As Long, Found6 As Range, FirstNumber As Range
[COLOR=#008000]  '
  '  Any previous existing code goes here
  '[/COLOR]
  Set Found6 = Cells.Find("Total", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
  LastRow = Cells(Rows.Count, Found6.Column).End(xlUp).Row
  If Found6 Is Nothing Then GoTo Skiddle
  Set FirstNumber = Cells(Evaluate(Replace("MIN(IF(E2:E#={2,3,4,5,6,7,8,9,10},ROW(E2:E#)))", "#", LastRow)), Found6.Column)
[COLOR=#008000]  '
  '  The FirstNumber range variable is now set to the first cell with a number between 2 and 10
  '
  '  Rest of your code goes here[/COLOR]
  '
 Skiddle:
End Sub

Note that I simplified your search for the Total column and eliminated the unnecessary selecting of cells.

Hi Rick,

That's almost got it. Is there a way to make E2:E# a variable? The "Total" column is a moving target usually around col Z or AA but cannot count on it being in the same place everytime.

Something like this (doesn't work but to give u the flavor):

ColNum = Found6.Column
Set FirstNumber = Cells(Evaluate(Replace("MIN(IF(ColNum ={2,3,4,5,6,7,8,9,10},ROW(ColNum)))", "#", LastRow)), Found6.Column)

thanks a million for your help.

 

[Rick Rothstein]

Hi Rick,

That's almost got it. Is there a way to make E2:E# a variable? The "Total" column is a moving target usually around col Z or AA but cannot count on it being in the same place everytime.

Sorry, I test with exact ranges to get the logic correct, then I go back and generalize the code for the stated conditions... I forgot to go back into the Evaluate function and change it. See if this code does what you want...

Sub Test()
  Dim LastRow As Long, Found6 As Range, FirstNumber As Range, Addr As String
[COLOR=#008000]  '
  '  Any previous existing code goes here
  '[/COLOR]
  Set Found6 = Cells.Find("Total", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
  If Found6 Is Nothing Then GoTo Skiddle
  LastRow = Cells(Rows.Count, Found6.Column).End(xlUp).Row
  Addr = Cells(2, Found6.Column).Resize(LastRow - 1).Address
  Set FirstNumber = Cells(Evaluate(Replace("MIN(IF(@={2,3,4,5,6,7,8,9,10},ROW(@)))", "@", Addr)), Found6.Column)
[COLOR=#008000]  '
  '  The FirstNumber range variable is now set to the first cell with a number between 2 and 10
  '
  '  Rest of your code goes here
  '[/COLOR]
Skiddle:
End Sub

 

[keith0528]

Sorry, I test with exact ranges to get the logic correct, then I go back and generalize the code for the stated conditions... I forgot to go back into the Evaluate function and change it. See if this code does what you want...

Sub Test()
  Dim LastRow As Long, Found6 As Range, FirstNumber As Range, Addr As String
[COLOR=#008000]  '
  '  Any previous existing code goes here
  '[/COLOR]
  Set Found6 = Cells.Find("Total", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
  If Found6 Is Nothing Then GoTo Skiddle
  LastRow = Cells(Rows.Count, Found6.Column).End(xlUp).Row
  Addr = Cells(2, Found6.Column).Resize(LastRow - 1).Address
  Set FirstNumber = Cells(Evaluate(Replace("MIN(IF(@={2,3,4,5,6,7,8,9,10},ROW(@)))", "@", Addr)), Found6.Column)
[COLOR=#008000]  '
  '  The FirstNumber range variable is now set to the first cell with a number between 2 and 10
  '
  '  Rest of your code goes here
  '[/COLOR]
Skiddle:
End Sub

Rick - Brilliant. That works perfectly. Best of all there is no looping. Not sure I understand how that last line works but it works.

thanks,
Keith