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

 

[Jonmo1]

What's not accurate?

The phrase "Something like"...
That's a pretty vague expression of what the value is.
Find requires an EXACT match.

 

[Vantom]

'Something like', as in one of thousands of possible date/time numbers with 10 decimals.

 

[Jonmo1]

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

 

[Vantom]

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.

 

[Jonmo1]

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]

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
	A	B	C	D	E
1	Original value	Shown with 10 decimals	Formatted as mm/dd/mm hh:ss:dd	Converted Back to 10 Decimal	Does it match original?
2	8/5/2015 12:51:44	42221.5359218750	08/05/2015 12:51:44	42221.5359259259	FALSE
Sheet1

 

[Vantom]

ADVERTISEMENT

How exactly do the date/time values get from sheet1 to the listboxes,

UserForm_Initialize

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

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

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

has the 42220.5678125 value(no 0s).

 

[Jonmo1]

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]

Seems to be working when I changed the 'xlWhole' to 'xlPart'

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

 

[Jonmo1]

Seems to be working when I changed the 'xlWhole' to 'xlPart'

Interesting, thus confirming the cells do not match 'Exactly'.

Anyway, glad you found a working solution.