You could probably do it using =vlookup() to examine every cell. But that could
take awhile.
How about this:
You build that table (column A is the old value, column B is the replacement).
Then the macro just cycles through that table and does a bunch of edit|Replaces?
Option Explicit
Sub testme()
Dim TableWks As Worksheet
Dim curWks As Worksheet
Dim myCell As Range
Dim myRng As Range
Set TableWks = Worksheets("sheet2")
Set curWks = Worksheets("Sheet1")
With TableWks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With
With curWks
For Each myCell In myRng.Cells
.Cells.Replace what:=myCell.Value, _
replacement:=myCell.Offset(0, 1).Value, _
lookat:=xlWhole, MatchCase:=False, _
searchorder:=xlByRows
Next myCell
End With
End Sub
souchie40 wrote:
What I really mean is that the 13 coloumns are from a table in access who's
data is based on a combo box so when exporting into excel its the ID number
abd not the text thats in the spread sheet what I want to do is using a macro
look up a second sheet in the same workbook to replace the value, but each
column is for different equipment but the ID numbers are the same.
"Dave Peterson" wrote:
If each column is supposed to look the same way (maybe all the entries are 8
characters with leading 0's), you could use a helper column with formulas like:
=text(a1,"00000000")
And drag down
Then you could copy that helper column and paste special|values and delete the
original column.
But I'm not sure if this what you meant.
souchie40 wrote:
Hi Everyone,
I have a spread sheet that is exported from access some of the cells have a
numeric value which should really be text - what I would like to do is run a
macro or code that will look at the cell and return a text value for the
given number, this would need to be repeated for 13 coloumns and hundreds of
rows
Many thanks in advance
--
Dave Peterson
--
Dave Peterson
|