If with Vlook up
 

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

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

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

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