Ensure cell content is text using VBA macro

[KG Old Wolf]

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

 

[hiker95]

KG Old Wolf,

Try:

With Cells(Rows, Columns)
  .NumberFormat = "@"
  .Value = X_Chg
End With

 

[KG Old Wolf]

Hiker! Yes, that worked... unfamiliar with the use of the "@" value in number formats so.... time to do a little more research on this end. Thanks a million for the prompt help.

Ken

 

[hiker95]

Ken,

You are welcome. Come back anytime.