VBA Now/Date Functions to Calculate Yesterday's Date for Use in String

[AlexB123]

Hi all,

I'm improving a macro and want to save the file according to the current naming conventions used. The files report numbers used for the current day and the day before, so the file names take the form "REVIEWED_07172018_07182018_TQREPORT.xlsx".

I can get today's date into the string using "Format(Now, "MMDDYYYY"), but I am getting an "Overflow" error when I try to calculate yesterday's date? I have tried:


Format(Now() - 1, "MMDDYYYY")
and
dayCount1 = Format(Now, "MMDDYYYY")
dayCount2 = dayCount1 - 1
and
Workday(Today(), -1)

Any suggestions are appreciated!

Please note: I have two functions that use MkDir to create any directories that do not exist.

    Dim tqLong, prepayLong As String
    Dim macroWB, mainWB, prepayWB, tqWB As Workbook
    Dim mainSh1, mainSh2, mainSh3, tqSh1, tqSh2, tqSh3, prepaySh1, macroSh1 As Worksheet
    Dim tpsUserCount, dayCount1, dayCount2 As Integer
    Dim lastrowMacSh As Integer: lastrowMacSh = 0
    Dim lastrowSh2 As Integer: lastrowSh2 = 0
    Dim lastrowSh3 As Integer: lastrowSh3 = 0
    Dim tpsUsers As Variant
    Dim sort1, sort2, sort3 As Range
    Dim archivePath1 As String
    Dim archivePath2 As String
    Dim archivePath3 As String
    Dim bkName, fileStr, fileName As String
        archivePath1 = "C:\Alex\Documents\"
        archivePath2 = archivePath1 & Year(Now) & " " & "Reviewed\"
        archivePath3 = archivePath2 & MonthName(Month(Now)) & "\"
                
            Call CheckForPaths(archivePath1, archivePath2)
            Call CheckOnePath(archivePath3)
        
                'Ask the user which report they are uploading
                bkName = InputBox(Prompt:="Please input either ""TQ41"" or ""TQ46"".", _
                    Title:="Which report?")
                        
                    If bkName <> "TQ41" And bkName <> "TQ46" Then
                        MsgBox "Please enter either ""TQ41"" or ""TQ46"" in to the message box!"
                        Exit Sub
                    End If
        
        dayCount1 = Format(Now, "MMDDYYYY")
        dayCount2 = Now() - 1
        
        fileStr = "REVIEWED_" & Format(dayCount2, "MMDDYYYY") & "_" & dayCount1 & " " & bkName & ".xlsx"
        fileName = archivePath3 & fileStr
[LEFT][COLOR=#222222][FONT=Verdana]

[/FONT][/COLOR][/LEFT]

 

[Joe4]

That is because the FORMAT function returns a string, and you cannot subtract one from a string.
Also, NOW returns the current date & time. You can just use DATE to return the current date.

So try:

dayCount1 = Format(Date, "MMDDYYYY")
dayCount2 = Format(Date - 1, "MMDDYYYY")

 

[AlexB123]

Many thanks!

Working wonderfully now!

 

[Joe4]

You are welcome.