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

Vantom

Board Regular
Joined
Feb 28, 2014
Messages
63
Code:
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'.
 
Last edited:

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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...

Code:
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

Board Regular
Joined
Feb 28, 2014
Messages
63
Structuring code?
"method of setting variables is incorrect"?
Don't need, but can!
It doesn't!
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,480
Office Version
  1. 2013
Platform
  1. Windows
your response doesn't help !
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
2,555

ADVERTISEMENT

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 ":"

Code:
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

Board Regular
Joined
Feb 28, 2014
Messages
63
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

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)

?
 
Last edited:

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Board Regular
Joined
Feb 28, 2014
Messages
63
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,744
Messages
5,573,975
Members
412,560
Latest member
mxoax32
Top