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:

Vantom

Board Regular
Joined
Feb 28, 2014
Messages
63
Another cause of errors was that numbers(for example date/time number ending in 1666) was rounded up(1667) when put in a named range(when date was clicked in a listbox, a named range was created with the date/time formatted as a 10 decimal number, and then the rounded up named ranges were written to the cells Sheet1!B5(FromDate)/Sheet1!B7(ToDate) that .find was to find. Solution is to write the date/time numbers directly to the cells Sheet1!B5(FromDate)/Sheet1!B7(ToDate)when a date is clicked in on of the listboxes, and then put the cell values in named ranges(as a result the date/time numbers does not get rounded up).
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
That's exactly what I've been trying to tell you since back in post #8.
The values changed at some point between going from the original sheet to another sheet, then to a listbox, then back to the sheet.
 

Vantom

Board Regular
Joined
Feb 28, 2014
Messages
63
Yes, .find searched for e.g. the variable 42220.579655(where 0s had been removed), when cell content in Sheet2 was 42220.5796550000, solved by 'LookAt' 'xlPart', instead of 'xlWhole'. And the rounded up numbers, solved by changing the order in which the date/time number are written to named range/cell, cell first, named second, insted of the other way around.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,750
Messages
5,574,016
Members
412,562
Latest member
woodportaj
Top