# Finding the first occurrence of a value and returning the row number - Run Time Error 91

#### wjsods74

##### New Member
Hello all,

I have been experiencing an undue amount of difficulty in applying various solutions to the problem I gave in the title.

To be clear, I am trying to find the first occurrence of a "0" in column F and keep receiving a "Run time error 91" erroe. I have made multiple attepmts at using solutions that people have posted on many sites, including this one. My current code is

-------------------------------------------------

Sub FindFirstZero()

Dim cell As Range
Dim FirstZero As Long

Set cell = Range("F:F").Find(What:="0", LookIn:=xlValues, _
LookAt:=xlWhole, SearchFormat:=False)

FirstZero = cell.Row

End Sub

-------------------------------------------------

and the error is being reported on the line "FirstZero = cell.Row".

I have tried using "Set FirstZero = cell.Row", but the error returned there is that an object is required...

I expect that I am missing something fundamental here as I have struggled with this type of error before. Any advice is much appreciated.

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
Welcome To The Board,
Here's A Code That Shows The First Zero Row In A MsgBox
Code:
Sub FindNextZero()
Dim Col As Range
Set Col = Range("F:F")
FirstZero = ""
For Each Cell In Col
If Cell.Value = 0 And IsEmpty(Cell) = False Then
FirstZero = Cell.Row
Exit For
End If
Next
If FirstZero <> "" Then
MsgBox FirstZero
Else
MsgBox "Column F Has No Zeros"
End If
End Sub

ZAX

If you would like to use the Find method, try this:
Code:
Sub FindFirstZero()
Dim cell As Range
Dim FirstZero As Long

On Error Resume Next
Set cell = Range("F:F").Find(What:="0", after:=Range("F" & Rows.Count), LookIn:=xlValues, _
LookAt:=xlWhole, SearchFormat:=False)
On Error GoTo 0
If Not cell Is Nothing Then
MsgBox cell.Row
Else
MsgBox "No 0 found in column F"
End If
End Sub

ZAX,

Thanks, it worked like a charm. Although I'm a little confused as to why the variable "FirstZero" doesn't need to be declared.

And I'll give JoeMo's approach a shot too... trying to get as much understanding as possible.

Say, could anyone point me toward some good VBA books, maybe beginner and intermediate (as I'm going to be using this for a long time). Thanks in advance!

Last edited:
It's Not Necessary To Declare Variables because the VBA Automatically Declares Them As a Variant,We Actually Declare Variants to Decrease The Bytes Employed By Them And You Can Check This in The VBA Books.

p.s: I didn't need to declare the variant Col,I only did this to make the macro look beautiful....

Last edited:
Well, aesthetics do count for something! Thanks again.

Replies
4
Views
511
Replies
4
Views
445
Replies
8
Views
435
Replies
12
Views
502
Replies
2
Views
227

1,203,060
Messages
6,053,303
Members
444,650
Latest member
bookendinSA

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