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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Vantom

Board Regular
Joined
Feb 28, 2014
Messages
63
'Something like', as in one of thousands of possible date/time numbers with 10 decimals.
 
Last edited:

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
How the cells are formatted is irrelevant.
The FIND function looks at the actual values contained in the cells.
Not how they appear to be when formatted in a certain way.

i.e.
You can format the cell containing 42213.6370138889 to show only 2 decimal places.
It would APPEAR to be 42213.64
But it's not, the actual value contained in the cell remains the same full value.
And FIND is looking at that actual full value.
So if you used find to look for 42213.64, it wouldn't find it.

So what exactly is in each of the 2 columns?
Do they BOTH contain TIME (decimal values after the .) values as well as the Dates (whole numbers before the .) ?
 
Last edited:

Vantom

Board Regular
Joined
Feb 28, 2014
Messages
63
So what exactly is in each of the 2 columns?
Do they BOTH contain TIME (decimal values after the .) values as well as the Dates (whole numbers before the .) ?
Sheet1:

Sheet2:


I copy Columns(A:A) in Sheet2(sorted ascending with duplicates). I paste it in a column in Sheet1, I sort it descending and remove the duplicates.

I put the dates/times in the Sheet1 column into two listboxes on a userform(FromDate listbox and ToDate listbox, formatted as "[$-3409]dddd, dd mmmm yyyy hh:mm:ss;@"). When a date/time gets clicked in either listbox, the date/time gets written in Sheet1!B5(FromDate)/Sheet1!B7(ToDate) formatted as a number with 10 decimals. I then want to find the row(Sheet1!B6(FromDateRow)/Sheet1!B8(ToDateRow) in which the clicked date/time can be found in Columns(A:A) in Sheet2(using whatever works, match, find etc?) without errors. The result I hope to achieve is to update a chart with one or more chartseries', found in Sheet2 Columns(B:B), Columns(C:C) etc, but only with the dates/times and rows between the FromDate I click in the FromDate listbox and/or the ToDate I click in the ToDate listbox.
 
Last edited:

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

How exactly do the date/time values get from sheet1 to the listboxes, and then from the listboxes into B5 and B7 ?
At some point in that process, the value that got into B5 changed (it's decimal value) from what it was originally in Sheet2!A:A

Do you want to email the book to me, it's very difficult to find the problem without having the actual book.
PM me if you want to do that.

Or post it on a file sharing site like DropBox or something.

DeSensitize it of coarse.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
I'm going to guess it's right at this step
I put the dates/times in the Sheet1 column into two listboxes on a userform(FromDate listbox and ToDate listbox, formatted as "[$-3409]dddd, dd mmmm yyyy hh:mm:ss;@").

As it seems you're aware, there are many different decimal values representing a given time up to the second.
Example: Both numbers
42221.5276481481
42221.5276481571
When formatted as a mm/dd/yyyy hh:mm:ss are translated to
08/05/2015 12:39:49
But the underlying numeric values are different, but they appear the same formatted as a date/time.

So with that, when you format the values in the listbox as "[$-3409]dddd, dd mmmm yyyy hh:mm:ss;@"
That action rounds the value off to the nearest second
Which is then 42221.527650463 in Decimal value.

And that will no longer be an exact match to the original value before it got put into the listbox.
Then when you take it from the listbox and put it into B5 you're putting in the rounded Date/Time.

You can see this in action below..
Formula in C represents your action of putting it in the listbox with the custom format.


Excel Workbook
ABCDE
1Original valueShown with 10 decimalsFormatted as mm/dd/mm hh:ss:ddConverted Back to 10 DecimalDoes it match original?
28/5/2015 12:51:4442221.535921875008/05/2015 12:51:4442221.5359259259FALSE
Sheet1
 
Last edited:

Vantom

Board Regular
Joined
Feb 28, 2014
Messages
63

ADVERTISEMENT

How exactly do the date/time values get from sheet1 to the listboxes,
UserForm_Initialize
Code:
With UserForm1.MultiPage1.ImportTicksPage.ListBox2: .RowSource = "Sheet1!AE1: AE" & lngLastRowAE: End With
With UserForm1.MultiPage1.ImportTicksPage.ListBox3: .RowSource = "Sheet1!AE1: AE" & lngLastRowAE: End With
and then from the listboxes into B5 and B7 ?
ListBox2_Click
Code:
With ActiveWorkbook
    .Names.Add Name:="TradeDumpFraDato", RefersTo:=Format(UserForm1.MultiPage1(0).ListBox2.Value, "0.0000000000")
        With Range("Sheet1!B5"): .Value = [TradeDumpFraDato]: .NumberFormat = "0.0000000000": End With
At some point in that process, the value that got into B5 changed (it's decimal value) from what it was originally in Sheet2!A:A
Sheet1:


Sheet2:


The 'vFromDate' variable as in
Code:
vFromDate = Sheets("Sheet1").Range("B5").Value
has the 42220.5678125 value(no 0s).
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Like I said, very difficult to troubleshoot without the actual book.
We can keep poking and guessing and probably never get there.

Can you share the book?
 

Vantom

Board Regular
Joined
Feb 28, 2014
Messages
63
Seems to be working when I changed the 'xlWhole' to 'xlPart'
Code:
lngDateRow = Rng.Find(What:=vFromDate, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row
 

Watch MrExcel Video

Forum statistics

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