How do I replace one variable with another for an entire column

user_11665873 0
Viewed 47

I am trying to replace all instances, in column D, of variable one to variable 2. Seems to work fine if I only use one variable, but i need both.

Find and replace works if i use the exact value and ONE variable, however i plan to share this code with others and they will need to define their own 2 variables. Example, in column D, PortName "Houston" will need to be changed to PortCode "1234". But if I give the script to Dallas they can define PortName to "Dallas", PortCode "5678"

Dim PortCode As Single
Dim PortName As Single
PortCode = 5301
PortName = HOUSTON

Columns("D:D").Select
Selection.Replace What:="" & PortName & "", _
                    Replacement:="" & PortCode & "", _
                    LookAt:=xlPart, _
                    SearchOrder:=xlByRows, _
                    MatchCase:=False, _
                    SearchFormat:=False, _
                    ReplaceFormat:=False

If i use only one variable and define the other with an actual value, it works fine. If i use both variables it does nothing. I want to change every instance of "Houston" to "1234", in column D only, using variables i can define.

find vba variables replace
1 Answers

Single is a data type used for describing "single-precision floating-point numbers".
You want String.
With that, you can simplify your code a bit:

Dim PortCode As String
Dim PortName As String
PortCode = "5301"
PortName = "HOUSTON"

Columns("D:D").Select
Selection.Replace What:=PortName , _
                Replacement:=PortCode , _
                LookAt:=xlPart, _
                SearchOrder:=xlByRows, _
                MatchCase:=False, _
                SearchFormat:=False, _
                ReplaceFormat:=False
user_32463620
Related