# Find First Occurrence of Value in a Column

#### keith0528

##### Active Member
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.
Code:
``````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

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### BobUmlas

##### Well-known Member
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

##### MrExcel MVP
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

##### Active Member
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

##### MrExcel MVP
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...
Code:
``````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.

Last edited:

#### keith0528

##### Active Member
Okay, give this code a try...
Code:
``````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):

Rich (BB code):
``````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

##### MrExcel MVP
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...
Code:
``````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
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

##### Active Member
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...
Code:
``````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
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

Replies
14
Views
153
Replies
4
Views
334
Replies
1
Views
201
Replies
1
Views
189
Replies
2
Views
205

### Forum statistics

1,190,700
Messages
5,982,371
Members
439,776
Latest member
mathewduffy ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back