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 <Jor...@discussions.microsoft.com> 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