russell-skmr3 wrote:
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

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