Code simplification

  • Thread starter Thread starter russell-skmr3
  • Start date Start date
R

russell-skmr3

Hi Everyone.
I have some code to change a month reference in a file
path variable. If possible could someone help me in
simplifying this code? Any help would be greatly
appreciated.

---code----

Sub ChangeDateInFilePath()

strcmonth = Range("A7").Value ' H:\Temp\Jul03\
strb1month = Left(strcmonth, 8) ' -> H:\Temp\
strb2month = Right(strcmonth, 3) ' -> 03\
strw1month = Right(strcmonth, 6) ' -> Jul03\
strw2month = Left(strw1month, 3) ' -> Jul

If strw2month = "Jan" Then
strw3month = "Feb"
ElseIf strw2month = "Feb" Then
strw3month = "Mar"
ElseIf strw2month = "Mar" Then
strw3month = "Apr"
ElseIf strw2month = "Apr" Then
strw3month = "May"
ElseIf strw2month = "May" Then
strw3month = "Jun"
ElseIf strw2month = "Jun" Then
strw3month = "Jul"
ElseIf strw2month = "Jul" Then
strw3month = "Aug"
ElseIf strw2month = "Aug" Then
strw3month = "Sep"
ElseIf strw2month = "Sep" Then
strw3month = "Oct"
ElseIf strw2month = "Oct" Then
strw3month = "Nov"
ElseIf strw2month = "Nov" Then
strw3month = "Dec"
ElseIf strw2month = "Dec" Then
strw3month = "Jan"
End If

strnewmonth = strb1month & strw3month & strb2month
Range("A9").Value = strnewmonth

End Sub
 
russell-skmr3 said:
Hi Everyone.
I have some code to change a month reference in a file
path variable. If possible could someone help me in
simplifying this code? Any help would be greatly
appreciated.

---code----

Sub ChangeDateInFilePath()

strcmonth = Range("A7").Value ' H:\Temp\Jul03\
strb1month = Left(strcmonth, 8) ' -> H:\Temp\
strb2month = Right(strcmonth, 3) ' -> 03\
strw1month = Right(strcmonth, 6) ' -> Jul03\
strw2month = Left(strw1month, 3) ' -> Jul

If strw2month = "Jan" Then
strw3month = "Feb"
ElseIf strw2month = "Feb" Then
strw3month = "Mar"
ElseIf strw2month = "Mar" Then
strw3month = "Apr"
ElseIf strw2month = "Apr" Then
strw3month = "May"
ElseIf strw2month = "May" Then
strw3month = "Jun"
ElseIf strw2month = "Jun" Then
strw3month = "Jul"
ElseIf strw2month = "Jul" Then
strw3month = "Aug"
ElseIf strw2month = "Aug" Then
strw3month = "Sep"
ElseIf strw2month = "Sep" Then
strw3month = "Oct"
ElseIf strw2month = "Oct" Then
strw3month = "Nov"
ElseIf strw2month = "Nov" Then
strw3month = "Dec"
ElseIf strw2month = "Dec" Then
strw3month = "Jan"
End If

strnewmonth = strb1month & strw3month & strb2month
Range("A9").Value = strnewmonth

End Sub
Click to expand...

I don't think your code really needs simplifing. But if you wanted to
replace the long If statement with the following, you could:

strw3month = Choose((Month(strw2month & "/1/2000")) Mod 12 + 1, _
"Jan", "Feb", "Mar", "Apr", "May", "Jun", _
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

Which is shorter but more complex.

Personally, I would I find the Select Case statement more readable:
Select strw2month
Case "Jan"
strw3month = "Feb"
Case "Feb"
strw3motnh = "Mar"
Case "Mar"
strw3motnh = "Apr"
etc.

Case else
Msgbox "Bad data - strw2month = " & strw2month
End Select


Regards,

Matthew Connor
 
Sub ChangeDateInFilePath()
Dim dtVal As Date
strcmonth = Range("A7").Value ' H:\Temp\Jul03\
strb2month = Mid(strcmonth, 9, 3)
strYear = "20" & Mid(strcmonth, 12, 2)

dtVal = DateValue(strb2month & " 1," & strYear)
strb3month = Format(DateSerial(2003, _
Month(dtVal) + 1, 1), "mmmyy")
strnewmonth = Application.Substitute(strcmonth, _
Mid(strcmonth, 9, 5), strb3month)
Debug.Print strcmonth, strnewmonth
End Sub


produced:
H:\Temp\Jul03\ H:\Temp\Aug03\
H:\Temp\Dec03\ H:\Temp\Jan04\

Regards,
Tom Ogilvy
 
Russell,
Try this

Sub ChangeDateInFilePath()

strcmonth = Range("A7").Value ' H:\Temp\Jul03\

'strb1month = Left(strcmonth, InStr(InStr(1, _
Range("A7"), "\") + 1, Range("A7"), "\")) ' -> H:\Temp\
strb1month = Left(strcmonth, Len(strcmonth) - 6)

Range("A9").Value = _
Left(Right(strcmonth, 6), 3)
Range("A9").Value = "1-" & Range("A9").Value & "-" & _
Left(Right(strcmonth, 3), 2)
Range("A9").Value = Range("A9").Value - 1
Range("A9").Value = _
Evaluate("date(" & Year([a9]) & "," & Month([a9]) + 2 & _
"," & "1" & ")")
Range("A9").Value = _
strb1month & Format([a9], "mmmyy") & "\"
End Sub
 
Back
Top