Ensure cell content is text using VBA macro

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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
KG Old Wolf,

Try:

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

KG Old Wolf

Board Regular
Joined
Sep 20, 2009
Messages
65
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,837
Messages
5,833,918
Members
430,244
Latest member
Ireland1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top