Having problems with Variables within my macro.

Posted by Chris Rock on November 14, 2001 2:35 PM

I have a macro that I've had some help with. The code I was given finds the Prior Monday from the time when the macro runs (and if it runs on a Tuesday it goes back to the Monday before):

If Weekday(Now, 2) = 1 Then
Date_1 = Format(Now - 6, "mm-dd-yy")
Else
Date_1 = Format(Now - (Weekday(Now, 2) + 6), "mm-dd-yy")
End If

My variable is called "Date_1". I am trying to create a second variable called "Date_2" which basically represents the SUNDAY following the MONDAY represented by Date_1.

I thought all I had to do was say Date_2 = Date_1 + 6, but this isn't working. I get a "type mismatch" error.

How can I set this variable up so that VBA know that I want to take the Date_1 variable and just add 6 to get Date_2?

Thanks in advance for anyone who can show me.

Posted by Barrie Davidson on November 14, 2001 2:40 PM

Chris, change the code to:

If Weekday(Now, 2) = 1 Then
Date_1 = Now - 6
Else
Date_1 = Now - (Weekday(Now, 2) + 6)
End If

The previous code was returning a text value (the format command).

Regards,
Barrie
Barrie Davidson

Posted by Chris Rock on November 14, 2001 3:01 PM

Barrie,
The change you suggested makes it easy to set Date_2 as a variable. However...a new problem exists:

I used the Previous version of the code to take the DATE of the prior Monday (formatted as mm-dd-yy) and use it in the filename.

Here's code that worked, until I tried to get the "date_2" variable:

Sub Save_sheet()

Dim SheetName As String
SheetName = ActiveSheet.Name
Dim WorkbookName As String

' If Weekday(Now, 2) = 1 Then
' Date_1 = Now - 6
' Else
' Date_1 = Now - (Weekday(Now, 2) + 6)
' End If

Format Date_1, "mm-dd-yy"

If Weekday(Now, 2) = 1 Then
Date_1 = Format(Now - 6, "mm-dd-yy")
Else
Date_1 = Format(Now - (Weekday(Now, 2) + 6), "mm-dd-yy")
End If

WorkbookName = SheetName & " " & Date_1 & " " & "thru" & " " '& Date_2

ActiveSheet.Copy

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\All Users\Desktop\Test\" & WorkbookName & ".xls "

Can you suggest how I might get a Date_2 variable to work there?

Thanks a whole bunch!

Posted by Chris Rock on November 14, 2001 3:07 PM

Barrie,
The change you suggested makes it easy to set Date_2 as a variable. However...a new problem exists:

I used the Previous version of the code to take the DATE of the prior Monday (formatted as mm-dd-yy) and use it in the filename.

Here's code that worked, until I tried to get the "date_2" variable:

Can you suggest how I might get a Date_2 variable to work there?

Thanks a whole bunch!

Posted by Chris Rock on November 14, 2001 3:36 PM

Hey...whatd'ya know, I got it working.

I just wrote code to generate "date_2" the same way you helped me get "date_1". Works like a charm.

Thanks for your help, Barrie.