1

I'm trying to extract a date from a spreadsheet that falls between 01/07/2019 and 31/07/2019 and save that in the variable chantalJulyTotalDemanded.

Dim julyStart As Date 
Dim julyFinish As Date 

julyStart = CDate("01/07/2019")
julyFinish = CDate("31/07/2019")

If CDate(dataSheet.Cells(x, 12)) >= CDate(julyStart) And _
  CDate(dataSheet.Cells(x, 12)) <= CDate(JulyFinish) Then
    chantalJulyTotalDemanded = chantalJulyTotalDemanded + dataSheet.Cells(x, 10)

The above also grabs information from dates in August.

The date from the spreadsheet is in the format 1/07/2019.

Community
  • 1
  • 1
Toby Garner
  • 11
  • 1
  • Please also note that the value in cell 12 is the date and 10 is the dollar value. And im also running a loop to through the entire spreadsheet – Toby Garner Oct 05 '19 at 07:52
  • `where I'm going wrong` - you are handling dates as strings. Make sure the cells contain [actual dates](https://superuser.com/q/299437/52365) and not strings that look like dates, use [date literals](https://stackoverflow.com/a/16078330/11683) or the `DateSerial()` function to initialize `julyStart` and `julyFinish`, and remove all the `CDate` calls. – GSerg Oct 05 '19 at 08:38
  • I cannot reproduce your problem with the information provided. Your code is sloppy, but seems to me it should not be returning August dates. – Ron Rosenfeld Oct 05 '19 at 13:00

1 Answers1

0

Less of an answer and more of suggestions:

For date constants in VBA code, use #

julyStart = #1/7/2019#

I'm not sure sure about how VBA determines if this is Jan 7 or Jul 1. But what I suggest you do is break in VBA and move mouse over vars to see what it shows you.

In your loop create a variable for the cell value:

Dim cellDate As Date:  cellDate = dataSheet.Cells(x, 12)

Now in break mode, you can inspect the variables to see if they are as you expect.

No need to run CDate on CDate(julyStart) since julyStart is already a date.

I'm assuming your date value in the cell is an actual date number value. If so, you shouldn't have any problem. But if it's a string value, then you'll need to figure out if CDate is performing the proper conversion of say 1/7/2019 vs 7/1/2019.

Ernie Thomason
  • 1,579
  • 17
  • 20