0

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
StormsEdge
  • 854
  • 2
  • 10
  • 35

1 Answers1

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
Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
  • 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. – StormsEdge Jan 06 '16 at 14:13
  • 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. – Scott Holtzman Jan 06 '16 at 14:17
  • Is it possible that because the dates are initialized to ranges rather than dates? – StormsEdge Jan 06 '16 at 14:18
  • @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. – Scott Holtzman Jan 06 '16 at 14:32
  • 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? – StormsEdge Jan 06 '16 at 14:37
  • @StormsEdge - See this [SO Post](http://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2) for more on `.Value` v `.Value2` While `Value2` is generally best. In some cases, such as this, you need `.Value`. – Scott Holtzman Jan 06 '16 at 14:49