increment a varialbe by 1

[smakatura]

background: I have files that have file names 06-01-11, 06-02-11....ie dates.

I am trying to return file names going in date order (part of a larger thing but this is the piece I have having issues with). for now, I am just trying to get a message box to show me the proper file name (one after the other). once that works I can build it into my larger macro

I am using

Dim filelocation As String
Dim rptmonth As String
Dim rptday As String
Dim rptyear As String
 
filelocation = "C:\Users\smakatura\Documents\call report\prep documents\test\" & rptmonth & "-" & rptday & "-" & rptyear
 
rptmonth = "06"
rptday = "01"
rptyear = "11"
 
Do Until rptday = 3
    MsgBox (filelocation)
    rptday = rptday + 1
Loop

when i try to put 01 instead of "01", it keeps reverting to just 1, but I need it to say 01 (that is until the number is greater than or equal to 10

 

[Jonmo1]

You have to recalculate the filelocation variable within the loop..

Do Until rptday = 3
    filelocation = "C:\Users\smakatura\Documents\call report\prep documents\test\" & rptmonth & "-" & rptday & "-" & rptyear
    MsgBox (filelocation)
    rptday = rptday + 1
Loop

 

[Scott Huish]

First I would use a date format, and then a For Next loop (a type of loop that is designed to increment)

Sub test()
Dim startdate As Date
Dim rptday As Long
startdate = #6/1/2011#
 
For rptday = 0 To 2
    filelocation = "C:\Users\smakatura\Documents\call report\prep documents\test\" & Format(startdate + rptday, "mm-dd-yy")
    MsgBox filelocation
Next
End Sub

 

[smakatura]

jonmo1's solution worked for my needs.


the reason I could not do a date formula is bc it is a file folder, so the 'date' is 06-02-11 not 06\02\11. but good idea, thanks

 

[Jonmo1]

jonmo1's solution worked for my needs.


the reason I could not do a date formula is bc it is a file folder, so the 'date' is 06-02-11 not 06\02\11. but good idea, thanks

That is why Hotpepper used the format function, to return the date using - instead of \

Format(startdate + rptday, "mm-dd-yy")

 

[smakatura]

ahh, since I already had rptmonth, rpt date and rpt year as variables, I just used the following loop after the increment to get the proper file name.

If rptday < 10 Then
             fullfilelocation = "C:\Users\smakatura\Documents\call report\prep documents\test\" & foldername & "\" & rptmonth & "-0" & rptday & "-" & rptyear
             Else: fullfilelocation = "C:\Users\smakatura\Documents\call report\prep documents\test\" & foldername & "\" & rptmonth & "-" & rptday & "-" & rptyear
        End If

basically just added the 0 in the right place if the rpt day was less than 10 (I acually did the previous part up to 31 instead of 3)