Hi there,

I am trying to develop a macro which performs a mass find/replace of
numbered street suffixes. For example:

"1st" = "1"
"2nd" = "2"
..
..
..
"3th" = "3'"

I am fairly familiar with VBA. However I have not really used it in the
excel environment before. So far I have created a user form that asks the
user to input the column in which the address field is located (A, B, C,
etc...) and then I have written some code to select that column. The next
step would be to loop through this column to find the street suffixes and
replace them with their substitutions. I am assuming I would now have to use
the Find() and Replace() functions to do this, but I am having a bit of a
hard time doing this. So here is what I have so far.

------------------------------------------------
Private Sub CB1_Click()
Dim AddressRange As Range
Dim sUserRange As String

sUserRange = TB1.Text

ActiveSheet.Columns(sUserRange).Select

End Sub
------------------------------------------------

Its not much, but I do not know how to use the find/replace functions.

Any help would be excellent.

Jordan

I hope there's a good reason that you're not selecting the entire
sheet / specific columns and doing a CTRL+F and replace....

Anywho ..

Private Sub CB1_Click()
Dim AddressRange As Range
Dim sUserRange As String

sUserRange = TB1.Text 'keep in mind that this text should be an
integer
ActiveSheet.Columns(sUserRange).Select 'now you have selected
column(s)

Selection.Replace What:="1st", Replacement:="2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False

Selection.Replace What:="2nd", Replacement:="2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False

Selection.Replace What:="3rd", Replacement:="2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False

End Sub


That's the simplest way (without knowing about the data you're working
with).

Remember, to learn more about code in excel, tools-macro-record new
macro ... do whatever .. hit 'stop' ... go into VB Editor (Alt+F11)
and see what's going on :)

Chris
On Mar 6, 4:25 pm, Jordan wrote:
Hi there,

I am trying to develop a macro which performs a mass find/replace of
numbered street suffixes. For example:

"1st" = "1"
"2nd" = "2"
.
.
.
"3th" = "3'"

I am fairly familiar with VBA. However I have not really used it in the
excel environment before. So far I have created a user form that asks the
user to input the column in which the address field is located (A, B, C,
etc...) and then I have written some code to select that column. The next
step would be to loop through this column to find the street suffixes and
replace them with their substitutions. I am assuming I would now have to use
the Find() and Replace() functions to do this, but I am having a bit of a
hard time doing this. So here is what I have so far.

------------------------------------------------
Private Sub CB1_Click()
Dim AddressRange As Range
Dim sUserRange As String

sUserRange = TB1.Text

ActiveSheet.Columns(sUserRange).Select

End Sub
------------------------------------------------

Its not much, but I do not know how to use the find/replace functions.

Any help would be excellent.

Jordan