Error Finding multiple date ranges in Excel List

Question

Getting an object variable or with variable not set error. All i'm trying to do is position myself to be able to analyze data from the newStartDate to the newEndDate and compare it to the data in between oldStartDate and oldEndDate. Stuck on the finding the addresses of the dates in the "Master List" Unsure why i'm struggling with this it's a simple task, but I can't seem to solve the error. Any thoughts? I have read the other posts similar to this and can't seem to figure out what i'm doing differently. Thanks in advance Code below:

Note: 1/1/14 is the date set in the range of newStartDate. This date does not exist as business was not conducted until the second. I had found in a post that that the "Set newStartDateFinder" line at the bottom would find the next closest date if the exact date was not found..i'm beginning to think that's not the case since when I attempt to run the debugger it appears to be empty.

Option Explicit
Sub Gather_Calculate_Performance()

'Variable declaration
Dim EBM As Workbook
Dim masterList, controlOut As Worksheet
Dim newStartDate As Range
Dim newEndDate As Range
Dim oldStartDate As Range
Dim oldEndDate As Range
Dim newStartDateFinder As Range
Dim newEndDateFinder As Range
Dim oldStartDateFinder As Range
Dim oldEndDateFinder As Range

'Setting main objects
Set EBM = Workbooks("Monthly Analysis (DEV)")
Set masterList = EBM.Sheets("Master List")
Set controlOut = EBM.Sheets("Control.Output")

'setting main variables
Set newStartDate = controlOut.Range("B" & 5)
Set newEndDate = controlOut.Range("B" & 6)
Set oldStartDate = controlOut.Range("D" & 5)
Set oldEndDate = controlOut.Range("D" & 6)

'Find addresses for dates
Set newStartDateFinder = masterList.Range("A:A").Find(newStartDate, LookIn:=xlValues, LookAt:=xlWhole)
Debug.Print newStartDateFinder.Address

End Sub

Try changing ...Find(newStartDate, ... in second last line to ...Find(newStartDate.Value, ... — PankajR
– PankajR, Commented Jan 6, 2016 at 13:56
@PankajR Still comes up empty, but I think the problem exists in the fact that 1/1/2014 doesn't exist, but it should then find 1/2/2014 instead. Don't think it is the proper code to do that? — StormsEdge
– StormsEdge, Commented Jan 6, 2016 at 13:57
yes, this is the problem @StormsEdge. Are you dates sorted in order? — Scott Holtzman
– Scott Holtzman, Commented Jan 6, 2016 at 13:58
@ScottHoltzman Yes sir they are. And I should add they always will be. — StormsEdge
– StormsEdge, Commented Jan 6, 2016 at 13:58

Scott Holtzman · Accepted Answer · 2016-01-06 14:31:50Z

1

Yes, the Find function will look for an exact match, if you set the LookAt argument to xlWhole. (Setting it to xlPart would find matches where the search string exists in part of the cell. So, the Find function won't work in the way you suspected here.

I was thinking you could use a VLOOKUP or MATCH function to find the next closest date to newStartDate but after testing, it didn't work the way I thought it would.

However, this loop while work. If I find a more efficient way, I will update the answer.

'Find addresses for dates
Dim i as Integer
i = 0

Do 

    Set newStartDateFinder = masterList.Range("A:A").Find(newStartDate.Value +i, LookIn:=xlValues, LookAt:=xlWhole)
    i = i + 1

Loop Until Not newStartDateFinder is Nothing

Debug.Print newStartDateFinder.Address

edited Jan 6, 2016 at 14:31

answered Jan 6, 2016 at 14:04

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

StormsEdge Over a year ago

Unfortunately I don't think I can utilize this method. The data set I am trying to analyze has nearly 100K rows in it. To loop through everyone of those looking for a date just froze my excel and I have to do that for each one. Thank you for the effort though I greatly appreciate it.

Scott Holtzman Over a year ago

you aren't looping through each row... you are checking for the newStartDate in column A and adding a day each time it can't find it. Presumably, it shouldn't take too many loops to find the first actual start date. Perhaps there is more about your situation I don't understand.

StormsEdge Over a year ago

Is it possible that because the dates are initialized to ranges rather than dates?

Scott Holtzman Over a year ago

@StormsEdge - I have edited my post. Use .Value instead of .Value2. Apologies. I got them mixed up. Also Dim masterList as Worksheet. The way you have it now, it's dimensioned as a Variant Object (not a Worksheet). I just tested it on a sample worksheet and it works.

StormsEdge Over a year ago

I will try that in a few moments and let you know. Thanks for your help as always! If you don't mind explaining, what's the difference between .value and .Value2?

|

Collectives™ on Stack Overflow

Error Finding multiple date ranges in Excel List

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related