Excel replace text.

[xcao]

I import a table from sql server to Excel occassionaly,

I need to format one column in Excel file.

For example, I need to replace numbers in the column with a text,
with weekly, 1 with daily, 16 with monthly etc, 4 with daily etc,

Everytime I get the new file, I have to do it use replace dialoge bo
on the edit menu to do all the replace,

Are there some easy ways to do this, or at least I can save thos
functions so that later I can use it again, instead of doing replac
for each one manually.



Thank

 

[JE McGimpsey]

One way:

Public Sub ConvertValues()
Dim rCell As Range
For Each rCell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
Select Case rCell.Value
Case 1, 4
rCell.Value = "daily"
Case 8
rCell.Value = "weekly"
Case 16
rCell.Value = "monthly"
Case Else
'do nothing
End Select
Next rCell
End Sub

 

[Tom Ogilvy]

Turn on the macro recorder while you do it manually - then turn off the
recorder when you are done. Then you can run this macro to do it.

 

[bdcrisp]

'Add The Following Function to the Sheet where you load the data to.

Function ReplaceValues(FindVal As Variant, ReplaceWithVal As Variant)

Cells.Replace What:=*FindVal*, Replacement:=*ReplaceWithVal,
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
SearchFormat:=False, ReplaceFormat:=False

End Function

'After You load the data, do a call for each replacement you want made
ex:

Call ReplaceValues("8","Daily")
Call ReplaceValues("1","Weekly"