VBA For Loop with Dates - argument not optional error-CodePudding

Running the code below works in one workbook but not another and I cannot work out why! The error line is the For d = DateSerial....

For Each dateKey In Json
       For d = DateSerial(Year(dateKey("dateFrom")), Month(dateKey("dateFrom")), Day(dateKey("dateFrom"))) To DateSerial(Year(dateKey("dateTo")), Month(dateKey("dateTo")), Day(dateKey("dateTo")))
        'Petrol
        ws.Cells(i, 1) = d
        ws.Cells(i, 2) = dateKey("petrol_1400")
        ws.Cells(i, 3) = dateKey("petrol_2000")
        ws.Cells(i, 4) = dateKey("petrol_9999")

        'Diesel
        ws.Cells(i, 6) = d
        ws.Cells(i, 7) = dateKey("diesel_1600")
        ws.Cells(i, 8) = dateKey("diesel_2000")
        ws.Cells(i, 9) = dateKey("diesel_9999")

        'LPG
        ws.Cells(i, 11) = d
        ws.Cells(i, 12) = dateKey("lpg_1400")
        ws.Cells(i, 13) = dateKey("lpg_2000")
        ws.Cells(i, 14) = dateKey("lpg_9999")

        i = i   1
    Next d
                 
skipDate:
    Next

Here are the referenced I use for this file:

This is the reference from the working workbook

The working workbook is where the module was written - the workbook that it is not working in has been built to check for code updates when it opens and then pull in any new modules/updated modules and run any code predefined to run. Does anyone have any ideas why I get this error and why it is in a simple for loop?

CodePudding user response：

Define d as a Date type dim d as date this solved the problem

CodePudding user response：

I think you are potentially getting a null value back from the JSON which is causing this error. If you run this code you'll get the same error:

a = DateSerial(2022, 1, 27)
Debug.Print (a)
a = DateSerial()
Debug.Print (a)

If so you will need to code in an If dateKey = null