Format a Column in Excel 1

[scking]

I have been attempting to determine how to format a column in Excel. It's relatively easy to determine the format using TypeName but have been unable to determine, using the help files, how to set the format of either individual cells or columns using either ranges or columns.

Steve King Growth follows a healthy professional curiosity

 

[dsi]

Use the NumberFormat property. Try recording a macro while you format cells and columns. Some of the values for NumberFormat are unique, but you'll get the idea. Here are a few examples.

Range("F3").NumberFormat = "0.00"
Range("D12").NumberFormat = "m/d"
Columns("C:C").NumberFormat = "# ?/2"

Hope this helps...

 

[SkipVought]

Hi scking ,

Thi is where the Macro Record feature shines. You can record the process and then see the code.
Skip,
metzgsk@voughtaircraft.com

 

[scking]

Thanks for the support. I've overcome the currency and numeric formats but am having problems with the text. When I use the .NumberFormat = "@", which is what the macro build it displays a string of character '5962012949766' as '5.9621E+12'. Then when I link from Microsoft Access the design of the linked object shows the column as numeric even though I have formatted it as text. What's up and what do I need to do to get it to display and read as real text (String)?

In the past I have forced the "'" control character in front of the string and then Access reads it as a text column. This doesn't suit my desire for using the correct functions.

Steve King

Steve King Growth follows a healthy professional curiosity

 

[euskadi]

Since you're already in VBA, why not grab the value of the cell, hold the value, reformat the cell to text, and then throw the value back in the cell?


mynumber = ActiveSheet.Range("c2&quot.Value
mytext = Format(mynumber, "0&quot

ActiveSheet.Range("c2&quot.NumberFormat = "@"
ActiveSheet.Range("c2&quot.Value = mytext

 

[scking]

This worked. Maybe not the best but success.

With ActiveSheet
Select Case FormatTo
Case TEXT
rngDeg = GetRange(2, ColumnIdx) & ":" _
& GetRange(GetLastRow, ColumnIdx)
For Each xCell In .Range(rngDeg)
varNumber = xCell.Value
varText = CStr(varNumber)
xCell.NumberFormat = "@"
xCell.Value = varText
Next xCell
Case NUM
.Columns(ColumnIdx).NumberFormat = "0"
Case CUR
.Columns(ColumnIdx).NumberFormat = "$#,##0.00"
Case Else
MsgBox "The format " & FormatTo & " is not recognized."
End Select
End With

Growth follows a healthy professional curiosity