CODEWITHSUNDEEP
excelmacrosvba
Emilie J
Emilie J

Reputation: 41

How do I get excel to search and change cell information in specific column?

I need to change all numbers from a specific column to text and the macros I'm using at the moment, changes all numbers in the whole database to letters, instead of just the specified column H. Here's the Macros I'm currently using. what should I add to the beginning of it to specify to search only in Column H? I'd like to keep the majority of my macro if possible. Thank you! 

    Sub EOD_Process_Part_2()
[![From and To][1]][1]    '----Changes all Numbers to initials----

Columns("H3:H").Select
    Application.ReplaceFormat.Clear
    Selection.Replace what:="1", Replacement:="AM", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace what:="2", Replacement:="B", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace what:="3", Replacement:="BM", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace what:="4", Replacement:="BS", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False

'--------------- adds missing and no initials-------------

    Columns("H:H").Select
    Range("H2").Activate
    With Application.ReplaceFormat.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Replace what:="", Replacement:="MISSING", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=True
    With Application.ReplaceFormat.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Application.ReplaceFormat.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.399945066682943
        .PatternTintAndShade = 0
    End With
    Selection.Replace what:="-", Replacement:="?", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=True
    Range("H2").Select

Selection.AutoFilter
Columns("H:H").ColumnWidth = 11.29
Range("L7").Select

Upvotes: 1

Views: 123

Answers (3)

Emilie J
Emilie J

Reputation: 41

This is my end result from what user Slai the Macros master created above. Thanks Slai! 

Sub EOD_Process_Part_2()

'-------Changes assigned numbers to right initials-------

     Application.ReplaceFormat.Clear
With ActiveCell.Worksheet.UsedRange.Range("H:H")
    .Replace "1", "AM", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
    .Replace "2", "B"
    .Replace "3", "BM"
    .Replace "4", "BS"

'-------Changes Blanks to MISSING with yellow background-------

    With Application.ReplaceFormat.Interior
        .PatternColorIndex = xlAutomatic
        .Color = XlRgbColor.rgbYellow
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    .Replace "", "MISSING", ReplaceFormat:=True

'-------Changes NO INITIAL to NO INITIAL with green background-------

    With Application.ReplaceFormat.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    .Replace "NO INITIAL", "NO INITIAL", ReplaceFormat:=True

'-------Changes - to ? with light blue background-------

    With Application.ReplaceFormat.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 14260581
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    .Replace "-", "?", ReplaceFormat:=True

'-------Centers Column H and adjusts the column's width-------

    Range("H2").Select
    Selection.AutoFilter
    Columns("H:H").ColumnWidth = 11.29
    Range("L7").Select
End With
End Sub

Thanks everyone for your help!

Upvotes: 0

Slai
Slai

Reputation: 22876

You can specify the range to replace without selecting it :

Dim columnH As Range  ' to specify that the variable columnH is of type Range
Set columnH = ActiveCell.Worksheet.UsedRange.Columns("H")
Application.ReplaceFormat.Clear

columnH.Replace "1", "AM", LookAt:=xlWhole, SearchFormat:=False, ReplaceFormat:=False
columnH.Replace "2", "B"
columnH.Replace "3", "BM"
columnH.Replace "4", "BS"

Application.ReplaceFormat.Interior.Color = XlRgbColor.rgbYellow 
columnH.Replace "" , "MISSING", ReplaceFormat:=True

Application.ReplaceFormat.Interior.Color =  XlRgbColor.rgbLightBlue
columnH.Replace "-", "?"

Another way can be to check the cells one by one 

Dim cell As Range, columnH As Range
Set columnH = ActiveCell.Worksheet.UsedRange.Columns("H")

For Each cell in columnH.Cells
    Select CStr(cell.Value)
        Case "1" : cell.Value = "AM"
        Case "2" : cell.Value = "B"
        Case "3" : cell.Value = "BM"
        Case "4" : cell.Value = "BS"

        Case ""  : cell.Value = "MISSING"
                   cell.Interior.Color = rgbYellow

        Case "-" : cell.Value = "?"
                   cell.Interior.ThemeColor = xlThemeColorLight2
                   cell.Interior.TintAndShade = 0.399945066682943
    End Select
Next

Upvotes: 1

Darrell H
Darrell H

Reputation: 1886

You need to define your range better. Avoid Select and Activate as much as possible. Don't search over 1 million rows if you don't have to. Determine the last row needed from the column and limit the number of rows to search.

For example:

Dim ws1 as Worksheet
Set ws1 = ThisWorkbook.Sheets("Sheet 1")

LastRow = ws1.Cells(Rows.Count, 8).End(Xlup).Row

Set SRng = ws1.Range(ws1.Cells(3,8),ws1.Cells(LastRow, 8))

With SRng
'Add in all your search and format stuff
End With

Upvotes: 1

Related Questions