Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
text argument in vlook up which has a "" | Excel Worksheet Functions | |||
vlook up | Excel Worksheet Functions | |||
Vlook up question - I think ? | Excel Discussion (Misc queries) | |||
How do I use drop down list selections/values in a vlook up formu. | Excel Discussion (Misc queries) | |||
"Vlook up + IF" | Excel Worksheet Functions |