Why 'Run-time error '91', Object variable or with block variable not set'

[Vantom]

With Sheets("Sheet2"): Dim Rng As Range: Set Rng = .Range("Sheet2!A:A"): End With
Dim lngDateRow As Long, vFromDate As Variant: vFromDate = Range("Sheet1!B5").Value
    With Sheets("Sheet2"): lngDateRow = Rng.Find(What:=vFromDate , LookIn:=xlValues, 
          LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False,
              SearchFormat:=False).Row: End With ': MsgBox lngDateRow

'vFromDate' is something like '42213.6370138889'.

 

[njimack]

That's a really strange way of structuring code - your method of setting variables is incorrect, and you don't need to use With/End With unless you're performing several actions on the same sheet. The below (untested) code should work for you...

Sub x()

Dim Rng As Range
Dim lngDateRow As Long, vFromDate As Variant


Rng = Sheets("Sheet2").Range("A:A")


vFromDate = Sheets("Sheet1").Range("B5").Value


lngDateRow = Rng.Find(What:=vFromDate, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row
MsgBox lngDateRow


End Sub

 

[Vantom]

Structuring code?
"method of setting variables is incorrect"?
Don't need, but can!
It doesn't!

 

[Michael M]

your response doesn't help !

 

[diddi]

vantom, the reason your code is "unusual" is because of you use of ":". I used to use it back in the days of C64s and apple 2e
here is your code without out the ":"

With Sheets("Sheet2")
    Dim Rng As Range
    Set Rng = .Range("Sheet2!A:A")
End With

Dim lngDateRow As Long, vFromDate As Variant
    vFromDate = Range("Sheet1!B5").Value
    With Sheets("Sheet2")
        lngDateRow = Rng.Find(What:=vFromDate , LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row
    End With
    MsgBox lngDateRow

in your first with block, you are saying
Set Rng = ("Sheet2").Range("Sheet2!A:A")
which has 2 problems
I don't think "Set" is required (but might be wrong there
but also you have referenced sheet2 twice
by setting out the code without ":", you can often see problems like that.

hope this helps

 

[Vantom]

Get the error on everything if columnwidth in sheet 2 ain't autofitted, and on every number which ends with one or more 0s when columnwidth is autofitted.

 

[Jonmo1]

First let's clarify...

There is nothing 'Wrong' with the way you wrote your code.
It's syntactically correct.

It's just not very common around here to see code written that way.
Hence the responses you've recieved..

Also, referring to a range like Sheets("Sheet2").Range("Sheet2!A:A") is not wrong, it's just redundant. But it will work fine.


Anyway, the code looks correct, the error is caused simply because the Find action didn't find what it was looking for.


Now to the issue..

'vFromDate' is something like '42213.6370138889'.

That's not a very accurate way of looking at things from a VBA programmers point of view.
Find deals with EXACT values.


It's basically looking for Sheet1!B5 within Sheet2!A:A
What does this formula return
=MATCH(Sheet1!B5,Sheet2!A:A,0)

?

 

[Jonmo1]

Looking at this again..

'vFromDate' is something like '42213.6370138889'.

Since the variable's name refers to DATE, I assume then the value in Sheet1!B5 is a DATE.
And the fact that the value has a decimal value, that means it also contains a TIME element.
So that number you posted, in Date/Time format is
7/28/2015 3:17:18 PM

Now if your dates in Sheet2 Column A are Dates only, no Time Values, then the match will never be found.
Because again it's looking for EXACT matches.

Try changing this
vFromDate = Sheets("Sheet1").Range("B5").Value
To
vFromDate = Int(Sheets("Sheet1").Range("B5").Value)

That will make it look at only the Date element of B5, and ignore the time.

Might also dim vFromDate As DATE instead of Variant.

 

[Vantom]

That's not a very accurate way of looking at things from a VBA programmers point of view.
Find deals with EXACT values.


It's basically looking for Sheet1!B5 within Sheet2!A:A
What does this formula return
=MATCH(Sheet1!B5,Sheet2!A:A,0)

?

What's not accurate?

The match formula gets the row for numbers that ends with one or more 0s, and a few others, but mostly returns #N/A

 

[Vantom]

Looking at this again..

'Sheets("Sheet1").Range("B5").Value' and 'Sheets("Sheet2").Range("A:A")' are both formatted as number with 10 decimals.