Dates condition in Macro

omypet

Board Regular
Joined
Oct 8, 2005
Messages
71
I have a macro that transfer data from other worksheet into a final worksheet and it’s condition is base on the filename with dates (example “myfile-2005-10-31.xls”), now the problem arises on “myfile-2005-11-01.xls” which is the Nov 1st, I have to rename the file to “myfile-2005-10-32.xls” which we don’t have Oct 32nd in order to continue picking data from the source files. This macro don't pick Nov 1. Do you have any suggestion on how to deal with dates especially for tomorrows date Nov. 1. Please !!!

Public MyLine
Sub NewDailyReports()
' by omypet
Mysheet = ActiveSheet.Name
mypath = ActiveWorkbook.Path
If Mysheet = "2005" Then
MyLine = ActiveCell.Row
FrmLine.TxtLine.Text = MyLine
FrmLine.Show
Mysheet = ActiveSheet.Name
Mydate = Range("A" & MyLine)
MycellA = "A" & MyLine
MycellB = "B" & MyLine
MycellC = "C" & MyLine
Do While Mydate < Date Or Mydate = Date And Mydate <> ""
MyDay = Day(Range("A" & MyLine)) + 1
If Len(MyDay) = 1 Then
MyDay = "0" & MyDay + 1
End If
MyMonth = Month(Range("A" & MyLine))
If Len(MyMonth) = 1 Then
MyMonth = "0" & MyMonth
End If
MyYear = Right(Year(Range("A" & MyLine)), 4)
MyDateFile1 = "myfile-" & MyYear & "-" & MyMonth & "-" & MyDay & ".xls"
If Dir(mypath & "\" & MyDateFile1) = "" Then
If Range(MycellB).Value = "" Then
Range(MycellB).Value = ""
End If
If Range(MycellC).Value = "" Then
Range(MycellC).Value = ""
End If
If Range(MycellD).Value = "" Then
Range(MycellD).Value = ""
End If
Else
FormulaB = "='" & mypath & "\[" & MyDateFile1 & "]Summary'!$K$13"
Range(MycellB).Formula = FormulaB
FormulaC = "='" & mypath & "\[" & MyDateFile1 & "]Summary'!$E$22"
Range(MycellC).Formula = FormulaC
FormulaD = "='" & mypath & "\[" & MyDateFile1 & "]Summary'!$E$23"
Range(MycellD).Formula = FormulaD
FormulaE = "='" & mypath & "\[" & MyDateFile1 & "]Production'!$M$9"
End If
MyLine = MyLine + 1
Mydate = Range("A" & MyLine)
MycellA = "A" & MyLine
MycellB = "B" & MyLine
MycellC = "C" & MyLine
MycellD = "D" & MyLine

Loop
End If
End Sub
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
If I'm understanding you correctly:

If you have set the Mydate variable as a date, you should simply be able to use Mydate+1 to return the next day.

For example, using a simple
Code:
Date+1
will return tomorrow's date.

So if the value of Mydate is 10/27/05, using Mydate + 1 should return 10/28/05. Same thing if Mydate is 10/31/05. Mydate + 1 should return 11/1/05.
 
Upvote 0
I started using this macro only on the 17th of this month Oct and seems to be working until 31st but not on Nov 1 anymore, I’ve tried mydate + 1 after the loop to get the next month result but to no avail.
 
Upvote 0
Not fully tested, but I believe it should get the dates correctly:

Code:
Public MyLine
Sub NewDailyReports()
Dim MySheet As String, MyPath As String
Dim MyDate As Date, MyDay As Integer
Dim MyYear As Integer, MyMonth As Integer
Dim MyDateFile1 As String
Dim MyCellA As Range, MyCellB As Range
Dim MyCellC As Range, MyCellD As Range
Dim FormulaB As String, FormulaC As String
Dim FormulaD As String, FormulaE As String

MySheet = ActiveSheet.Name
MyPath = ActiveWorkbook.Path

If MySheet = "2005" Then
    MyLine = ActiveCell.Row
    'FrmLine.TxtLine.Text = MyLine
    'FrmLine.Show
    MyDate = Cells(MyLine, "A").Value

    Do While IsDate(MyDate) = True And _
    MyDate < Date Or MyDate = Date
        Set MyCellA = Cells(MyLine, "A")
        Set MyCellB = MyCellA.Offset(, 1)
        Set MyCellC = MyCellB.Offset(, 1)
        Set MyCellD = MyCellC.Offset(, 1)
        
        MyDateFile1 = "myfile-" & Format(MyDate, "yyyy-mm-dd") & ".xls"
        MsgBox MyDateFile1
        
        If Dir(MyPath & "\" & MyDateFile1) = "" Then
            If MyCellB.Value = "" Then MyCellB.Value = ""
            If MyCellC.Value = "" Then MyCellC.Value = ""
            If MyCellD.Value = "" Then MyCellD.Value = ""
        Else
            FormulaB = "='" & MyPath & "\[" & MyDateFile1 & "]Summary'!$K$13"
            MyCellB.Formula = FormulaB
            
            FormulaC = "='" & MyPath & "\[" & MyDateFile1 & "]Summary'!$E$22"
            MyCellC.Formula = FormulaC
            
            FormulaD = "='" & MyPath & "\[" & MyDateFile1 & "]Summary'!$E$23"
            MyCellD.Formula = FormulaD
            
            FormulaE = "='" & MyPath & "\[" & MyDateFile1 & "]Production'!$M$9"
        End If
        
        MyLine = MyLine + 1
        MyDate = Cells(MyLine, "A").Value
    
    Loop
End If

End Sub
 
Upvote 0
Tks a lot von pookie for this idea you've shared, setting dim looks will works with this one. Appreciated your help
 
Upvote 0

Forum statistics

Threads
1,212,056
Messages
6,105,648
Members
447,974
Latest member
misspancake

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top