Replace Macro HELP!!!!!!!!

Posted by George on March 12, 2001 2:00 AM

Hi,

I'm trying to get a macro to replace numbers on to names. ie. Number 1 will be "UK", number 2 "Spain" etc.. using this macro:

Columns("A:A").Replace _
What:="1", Replacement:="UK"
Columns("A:A").Replace _
What:="2", Replacement:="Spain"

It all works OK until I get to numbers above 10, ie 12 when instead of replacing it with the correct name, it replaces it with "UK" (1) and "Spain" (2).

Any ideas?

Thanks

George

Posted by Dave Hawley on March 12, 2001 2:30 AM

Hi George

Here is a more efficient way to do this.

In Column B put a list of your replacement text i.e B1=UK ,B2=Spain etc

Then try this code:

Sub TryThis()
Dim i As Integer
Dim RplText As String
i = 1
Do Until i = WorksheetFunction.CountA(Columns(2)) 'Column B
RplText = Cells(i, 2) 'Column B
If WorksheetFunction.CountIf(Columns(1), CStr(i)) &LT;&GT; 0 Then
Columns(1).Replace What:=CStr(i), Replacement:=RplText, LookAt:=xlWhole
i = i + 1
End If
Loop
End Sub

Dave

BTW it is because you don't have the "LookAt:=xlWhole" argument that your code is causing you grief.

OzGrid Business Applications