check entire column on a specific value and change it

**Aquila** · 07-08-2010, 05:57 PM

Hello there,

I'm looking for VBA code to check entire column A in specific sheet "sheet3" on a specific value "SEC55B". If there is a value change it to "SEC550".

Can somebody help me out please.

Thnx a lot
Aquila

**shg** · 07-08-2010, 06:03 PM

Record a macro.

**Aquila** · 07-08-2010, 06:27 PM

Originally Posted by shg

Record a macro.

Hello shg,

it's not clear to me how to do this by recording a macro

i do record => CNTR+F (the cel is now activated)
then i go to the cell and change the value
this is the code i have in VBA, looks not pretty good to run?

Sub check55()
'
' check55 Macro
' De macro is opgenomen op 9-7-2010 door Aquila.
'

'
    Sheets("PRSrapport").Select
    Range("A21").Select
    ActiveCell.FormulaR1C1 = "SEC55B"
End Sub

Some words to clear out would be appreciated

Thnx
Aquila

**shg** · 07-08-2010, 06:46 PM

When I recorded a macro doing that, here's what I got:

Sub Macro1()
    Columns("A:A").Select
    Selection.Replace What:="SEC55B", Replacement:="SEC550", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

... which I would edit to this:

Sub Macro1()
    Worksheets("Sheet3").Columns("A").Replace _
            What:="SEC55B", _
            Replacement:="SEC550", _
            LookAt:=xlPart, _
            MatchCase:=False, _
            SearchFormat:=False, _
            ReplaceFormat:=False
End Sub