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

[wjsods74]

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.

 

[ZAX]

Welcome To The Board,
Here's A Code That Shows The First Zero Row In A MsgBox

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

 

[JoeMo]

If you would like to use the Find method, try this:

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

 

[wjsods74]

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!

 

[ZAX]

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

 

[wjsods74]

Well, aesthetics do count for something! Thanks again.