MrExcel.com - Your One Stop for Excel Tips & Solutions

Can I use "Case" instead of this repeating macro?

Posted by Nils on May 25, 2000 8:08 AM

I recorded a macro to change a field from date "2000/01 to Jan" and I copied it for the rest of the months. Its a long and cumbersome macro with a lot of repeats, so I thought I could use Case instead, but I'm not sure how I would set it up. (I'm beginner Excel macro's / VB). If Case can not be used can I use an "If" statement?
Thanks for any help.

Sub ReplaceMonth()
'
' ReplaceMonth Macro
' Macro recorded 3/27/00 by Nils Christensen
'
Selection.CurrentRegion.Select
Selection.Sort Key1:=Range("B2"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
' quarter 1
' Jan
Cells.Replace What:="????/01", _
Replacement:="Jan", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
' Feb
Cells.Replace What:="????/02", _
Replacement:="Feb", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
' Mar
Cells.Replace What:="????/03", _
Replacement:="Mar", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End Sub

Thank you

Posted by Celia on May 25, 2000 3:01 PM

ReplaceMonth Macro Macro recorded 3/27/00 by Nils Christensen


Nils
I presume that the cells you want to change are not formatted as Date (otherwise you do not need a macro - just change the format).

Here's some code using an If statement. It has been assumed that the cells to be changed are all in column A.

Dim cell As Range
Dim fieldToCheck As Range
Set fieldToCheck = Range(Range("A1"), Range("A65536").End(xlUp))
For Each cell In fieldToCheck
If cell = "2000/01" Then
cell = "Jan"
ElseIf cell = "2000/02" Then
cell = "Feb"
ElseIf cell = "2000/03" Then
cell = "Mar"
End If
Next


Posted by Celia on May 25, 2000 3:09 PM

ReplaceMonth Macro Macro recorded 3/27/00 by Nils Christensen


P.S.
If the dates to be changed might have years other than 2000, then the code should be :-

Dim cell As Range
Dim fieldToCheck As Range
Set fieldToCheck = Range(Range("A1"), Range("A65536").End(xlUp))
For Each cell In fieldToCheck
If Right(cell, 2) = "01" Then
cell = "Jan"
ElseIf Right(cell, 2) = "02" Then
cell = "Feb"
ElseIf Right(cell, 2) = "03" Then
cell = "Mar"
ElseIf Right(cell, 2) = "04" Then
cell = "Apr"
End If
Next


Posted by Nils on May 25, 2000 4:18 PM

Celia,
Thank you. This is what I needed, your second suggestion is perfect. And you are correct that the data is not in Date format. I'm Importing a large fixed width text file from an UNIX program. Then formatting the data.
Thank you Again
Nils