B
Blondegirl
Hello. On a form which adds a record to the active sheet, I am trying
to call a choice of 3 functions from their modules, using Select Case
where whatever the result of the function gets placed into the relevant
cell on the active sheet. I can't get it to work and was wondering if
someone could show me what is missing from it. I am getting error 449
(argument not optional) on the line ': Application.run' etc. Here is
the relevant part of my coding:
Select Case ActiveSheet.Name
Case "Sheet 1", _
"Sheet 2", _
"Sheet 3" _
: Application.Run "StartofMonth5yrs"
Cells(Nextrow, 3) = StartOfMonth5yrs(Date)
Case "Sheet 4", _
"Sheet 5", _
"Sheet 6" _
: Application.Run "StartofMonth6yrs"
Cells(Nextrow, 3) = StartOfMonth6yrs(Date)
Case "Sheet 7", _
"Sheet 8" _
: Application.Run "StartOfMonth2yrs"
Cells(Nextrow, 3) = StartOfMonth2yrs(Date)
End Select
'Here is one of the 3 function codes which takes today's date, and
calculates to the first day of the following month adding either 5, 6
or 2 years depending on the Select Case choice:
Function StartOfMonth5yrs(InputDate)
If IsDate(InputDate) Then
StartOfMonth5yrs = DateSerial(Year(InputDate) + 5,
Month(InputDate) + 1, 1)
Else
StartOfMonth5yrs = Empty
End If
End Function
Thank you.
to call a choice of 3 functions from their modules, using Select Case
where whatever the result of the function gets placed into the relevant
cell on the active sheet. I can't get it to work and was wondering if
someone could show me what is missing from it. I am getting error 449
(argument not optional) on the line ': Application.run' etc. Here is
the relevant part of my coding:
Select Case ActiveSheet.Name
Case "Sheet 1", _
"Sheet 2", _
"Sheet 3" _
: Application.Run "StartofMonth5yrs"
Cells(Nextrow, 3) = StartOfMonth5yrs(Date)
Case "Sheet 4", _
"Sheet 5", _
"Sheet 6" _
: Application.Run "StartofMonth6yrs"
Cells(Nextrow, 3) = StartOfMonth6yrs(Date)
Case "Sheet 7", _
"Sheet 8" _
: Application.Run "StartOfMonth2yrs"
Cells(Nextrow, 3) = StartOfMonth2yrs(Date)
End Select
'Here is one of the 3 function codes which takes today's date, and
calculates to the first day of the following month adding either 5, 6
or 2 years depending on the Select Case choice:
Function StartOfMonth5yrs(InputDate)
If IsDate(InputDate) Then
StartOfMonth5yrs = DateSerial(Year(InputDate) + 5,
Month(InputDate) + 1, 1)
Else
StartOfMonth5yrs = Empty
End If
End Function
Thank you.