Find/Replace Cell Values with Named Ranges

[enk529]

Hi,

I have a number of named ranges that I need to find/replace data in.

For example, in range "PAPKGC" there are three variables: F, P, and X. For "F", the cell needs to contain the word "Flat". "P" is to contain "Package" and "X" is to be blank. Range "PASIDE" also contains 2 variables. I am to concatenate any cells with a value of 2 with "/pk" and any cells with a value of 1 are to be blank.

So my big question is: How can I use the named ranges to find and replace this data using a VBA macro?

This is what I'm trying, but I have no idea what I'm doing.

Sub ReplaceText()
    Dim rngData As Range, rngCell As Range
         
    With ThisWorkbook
        Set rngData = Range("PAPKGC")
    End With
     'define the data range to evaluate
     
    For Each rngCell In rngData
        Select Case rngCell.Value
            Case "F"
                cell.Value = "Flat"
            Case "P"
                cell.Value = "Package"
        End Select
    Next rngCell
End Sub

Any help is appreciated!

-G

[StephenR]

Do you mean that if a cell has 'F' in it you want to replace it with 'Flat'? If so, why not just use Find and Replace from the worksheet?

[seckert]

Try something like this:

Sub ReplaceText()
With Range("MyRange")
    .Replace What:="F", Replacement:="Flat"
    .Replace What:="P", Replacement:="Package"
End With
End Sub

Hope this helps

SAE