Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Code simplification
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default Code simplification
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 17
Default Code simplification
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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Code simplification
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-skmr3 wrote in message
...
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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default Code simplification
Thanks Tom, Thanks Matthew

-----Original Message-----
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
.

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 46
Default Code simplification
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

"russell-skmr3" wrote in message
...
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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Simplification of Formula lmavroff Excel Discussion (Misc queries) 3 April 13th 10 03:32 PM
Formula Simplification Please ... Ken Excel Discussion (Misc queries) 6 May 9th 09 08:24 AM
Code simplification Sandy Excel Worksheet Functions 3 June 30th 07 08:11 PM
Simplification help Mike Smith NC Excel Worksheet Functions 3 July 12th 06 06:28 PM
formula simplification Todd Excel Worksheet Functions 2 October 28th 04 01:49 AM


All times are GMT +1. The time now is 03:48 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement