Macro to read column and depending on text, be renamed for standardization

I am looking to create a macro to rename cells under the "Severity" column if it reads one or more things to one standardized field to create charts and pivots with. Below is what I would like the cell to display if it reads any of these texts.

If cell displays: "Severity 1 - Critical" OR "S1" = S1
If cell displays: "Severity 2 - Major" OR "S2" = S2
If cell displays: "Severity 3 - Minor" OR "S3" = S3
If cell displays: "Severity 4 - Cosmetic" OR "S4" = S4
If cell displays: "Developer Support" = Developer Support

If the cell is blank, then the cell can remain blank.
Report.xlsx
AckeemKAsked:
Who is Participating?
 
Rodney EndrigaData AnalystCommented:
This code may do the same thing as Saurabh:

Sub EE_ChangeCellValue()
Dim l As Long, rng As Range
l = Cells(Rows.Count, 3).End(xlUp).Row
Set rng = Range("C1:C" & l)     ' You can change this to the proper COLUMN; Starts in ROW 1
For Each cell In rng
    Select Case LCase(cell.Value)   ' If you have additional criteria, type it in LOWER CASE below
        Case "severity 1 - critical", "s1": cell.Value = "S1"
        Case "severity 2 - major", "s2": cell.Value = "S2"
        Case "severity 3 - minor", "s3": cell.Value = "S3"
        Case "severity 4 - cosmetic", "s4": cell.Value = "S4"
        Case Else: cell.Value = cell.Value
    End Select
Next cell
Cells(1, 3).Select
End Sub

Open in new window


This assumes your data is in Column C. It will start its check in Row 1.
0
 
Saurabh Singh TeotiaCommented:
You can use the following code which will do what you are looking for...

Sub changedata()

    Dim rng As Range
    Dim lrow As Long

    lrow = Cells(Cells.Rows.Count, "C").End(xlUp).Row

    Set rng = Range("C2:C" & lrow)

    rng.Replace What:="Severity 1 - Critical", Replacement:="S1", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    rng.Replace What:="Severity 2 - Major", Replacement:="S2", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    rng.Replace What:="Severity 3 - Minor", Replacement:="S3", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    rng.Replace What:="Severity 4 - Cosmetic", Replacement:="S4", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

    MsgBox "Done"
End Sub

Open in new window


Alternatively you can use Ctrl+H which is replace functionality in excel to do the same thing which is what i'm using in the macro to do what you are looking for..

Saurabh...
0
 
AckeemKAuthor Commented:
Is there a way to have this macro run in the "Current" tab of the workbook if I set it as a command button (Update Links) on a "Menu" tab?
0
 
Saurabh Singh TeotiaCommented:
Change this line to..

    lrow = Cells(Cells.Rows.Count, "C").End(xlUp).Row

    Set rng = Range("C2:C" & lrow)

Open in new window


To this..

    lrow = Sheets("Current").Cells(Cells.Rows.Count, "C").End(xlUp).Row

    Set rng = Sheets("Current").Range("C2:C" & lrow)

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.