Help with Find and Replace numbers with autofilter on selected column

[decadence]

Hi, I am trying to replace certain numbers with numbers in a selected column that has been filtered on a specific number in that column but can't get my head around it. but I need to be able to filter on the column selected. Also I am having trouble with the number changing visually when a 0 is at the start This is what I have so far.

Dim rngAddress As Range
  Set rngAddress = Range("G1:H1").Find("Foot")
  If rngAddress Is Nothing Then
    Exit Sub
  End If
  Range(rngAddress, rngAddress.End(xlDown)).Select
    Selection.NumberFormat = "@"

'''Filter on number 603 using the selected column'''

    Selection.Replace What:="603", Replacement:="0603", LookAt:=xlPart, SearchOrder:=xlByRows

''' Filter off '''

Can someone help with this please.

 

[steve the fish]

Whats the filter for?

 

[decadence]

This is what I am trying to filter and replace on but only if found

    Selection.AutoFilter
    ActiveSheet.Range("Column G or H to the end of filtered data?").AutoFilter Field:=1, Criteria1:="201"
    Selection.Replace What:="201", Replacement:="0201", LookAt:=xlPart, SearchOrder:=xlByRows
    Selection.AutoFilter

    Selection.AutoFilter
    ActiveSheet.Range("Column G or H to the end of filtered data?").AutoFilter Field:=1, Criteria1:="402"
     Selection.Replace What:="402", Replacement:="0402", LookAt:=xlPart, SearchOrder:=xlByRows
    Selection.AutoFilter

    Selection.AutoFilter
    ActiveSheet.Range("Column G or H to the end of filtered data?").AutoFilter Field:=1, Criteria1:="603"
     Selection.Replace What:="603", Replacement:="0603", LookAt:=xlPart, SearchOrder:=xlByRows
     Selection.AutoFilter

    Selection.AutoFilter
    ActiveSheet.Range("Column G or H to the end of filtered data?").AutoFilter Field:=1, Criteria1:="805"
     Selection.Replace What:="805", Replacement:="0805", LookAt:=xlPart, SearchOrder:=xlByRows
     Selection.AutoFilter

 

[steve the fish]

You don't need to filter to replace. Just use xlWhole instead of xlPart. Your columns need to be formatted as text before you do the replace.

 

[decadence]

I know I don't need to but I might get other data in the column which might contain these numbers in a longer string, all i have to do is add match case set to true in the code The first bit of code above shows I am formatting it into text before replacing. The issue is that the 0 I want in front of the numbers is not showing up and is staying as if the format is in general i.e. 201,402,603,0805.

 

[steve the fish]

Do you want to add zeroes to just specific values or all numbers? This may help?

lr = Application.Max(Range("G" & Rows.Count).End(xlUp).Row, Range("H" & Rows.Count).End(xlUp).Row)
Set Rng = Range("G1:H" & lr)
Rng.NumberFormat = "@"
For Each c In Rng.Cells
    Select Case c.Value
        Case 201: c.Value = "0201"
        Case 402: c.Value = "0402"
        Case 603: c.Value = "0603"
        Case 805: c.Value = "0805"
    End Select
Next

 

[decadence]

It is just specific values that I stated above. the code works great, had to turn off the numbers stored as text error settings, Thanks for your help it's much appreciated.