VBA Subtracting dates

[knacksc2]

I am creating a report from a data file. I want to drop the difference in days from the date in column 11 from today into column 13. this seems like it should work...

Sub Printed_Not_Packed()


Dim FPathIM As String
Dim FPathBIA As String
Dim FPathIOS As String


FPathIM = "U:\Inventory Management\"
FPathBIA = "U:\Inventory Management\Branch Inventory Analysis\"
FPathIOS = "U:\Inventory Management\Interbranch Orders status\"


FName1 = Format(Date, "YYYYMMDD") & " ~~~~~~~~~~~~~~Printed and Not Packed" & ".xlsx"






Workbooks.Open Filename:="U:\Inventory Management\Printed and Not Packed.XLS"
ActiveWorkbook.SaveAs Filename:=FPathIM & FName1 _
    , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False


Workbooks(FName1).Activate


ActiveSheet.Cells(1, 13).Value = "AGE"
ActiveSheet.Cells(1, 14).Value = "OVER 7 DAYS"
ActiveSheet.Cells(1, 15).Value = "ORDER QTY OVER 7 DAYS"


For i = 1 To 65000
Workbooks(FName1).Worksheets("Sheet1").Cells(1 + i, 13).Value = (DateValue(DateSerial(Year(Now()), Month(Now()), Day(Now())))) - DateValue(Workbooks(FName1).Worksheets("Sheet1").Cells(1 + i, 11).Value) - 1
Next i

 

[knacksc2]

i think i solved this, but i am not sure why... this seems to be working

ActiveWorkbook.Worksheets("Sheet1").Cells(1 + i, 13).Value = (DateValue(DateSerial(Year(Now()), Month(Now()), Day(Now())))) - DateValue(ActiveWorkbook.Worksheets("Sheet1").Cells(1 + i, 11).Value) - 1

also, there may have been one row missing a value, that could cause the error, but it should repeat all the way up to the point where the blank occurs. so... yeahstillnot sure why it wouldn't work.

 

[Joe4]

One little tip.

I think this whole part:

(DateValue(DateSerial(Year(Now()), Month(Now()), Day(Now()))))

can simply be replaced with this:

Date

which returns the current date (with no time component)

 

[knacksc2]

Great, thanks for that. makes it much easier to read and write in the future. awesome tip!!

 

[Joe4]

You are welcome!