KG Old Wolf
Board Regular
- Joined
- Sep 20, 2009
- Messages
- 65
All,
This seems so easy but it is eluding me. The end result is to produce a table that has a column of 5 digit values - sometimes alpha and other times numeric. I want every value to be TEXT... never numeric. (The field is to be used as a Lookup key going up against a table in which all the entries are in Text).
I can get what I want using =Text(x,"00000") but I want to execute this within a VBA macro. Using the Application.WorksheetFunction.Text(x,"00000") will convert the variable to the text value I want but it reverts to a numeric value as soon as I post the work field back to the cell.
i.e.
X_Chg = Application.WorksheetFunction.Text(Cells(Rows,Columns))
X_Chg_test = Application.WorksheetFunction.IsText(X_Chg)
MsgBox(X_Chg_Test)
Cells(Rows,Columns) = X_Chg
I a message box to display if X_Chg "isText" and it shows TRUE but after the macro completes and I test the field, it comes back FALSE.
All I want to do is ensure that what I place in Cells(Rows,Columns) will always be in TEXT format.... what am I missing?
Thanks in advance
This seems so easy but it is eluding me. The end result is to produce a table that has a column of 5 digit values - sometimes alpha and other times numeric. I want every value to be TEXT... never numeric. (The field is to be used as a Lookup key going up against a table in which all the entries are in Text).
I can get what I want using =Text(x,"00000") but I want to execute this within a VBA macro. Using the Application.WorksheetFunction.Text(x,"00000") will convert the variable to the text value I want but it reverts to a numeric value as soon as I post the work field back to the cell.
i.e.
X_Chg = Application.WorksheetFunction.Text(Cells(Rows,Columns))
X_Chg_test = Application.WorksheetFunction.IsText(X_Chg)
MsgBox(X_Chg_Test)
Cells(Rows,Columns) = X_Chg
I a message box to display if X_Chg "isText" and it shows TRUE but after the macro completes and I test the field, it comes back FALSE.
All I want to do is ensure that what I place in Cells(Rows,Columns) will always be in TEXT format.... what am I missing?
Thanks in advance