Formatting Help

B

Bill

Hello,
I put the number 1 in cell A1 and then select the cell. Then I run the
following code:

MsgBox ActiveCell.NumberFormat
varformat = ActiveCell.NumberFormat
x = ActiveCell.Value
x = Format(xbar, varformat)
MsgBox xbar


I get "General" as expected for the first message box, but I get "Ge0eral"
for the second. How come I don't get 1?

I want to read a format in a certain cell and apply that it to variable in
my code that I print out in the title of a chart.

Thanks.

Bill
 
D

Dave Peterson

I used this version of your code:

Option Explicit
Sub testme()

Dim varFormat As String
Dim x As Variant
Dim xbar As Variant

MsgBox ActiveCell.NumberFormat
varFormat = ActiveCell.NumberFormat
xbar = 0
x = Format(xbar, varFormat)
MsgBox x
End Sub

But there's a difference between the worksheet function =Text() and the VBA
function Format.

Option Explicit
Sub testme2()

Dim varFormat As String
Dim x As Variant
Dim xbar As Variant

MsgBox ActiveCell.NumberFormat
varFormat = ActiveCell.NumberFormat
xbar = 0
x = Application.Text(xbar, varFormat)
'or if you know it's General in the worksheet function
'x = format(xbar,"general number")
MsgBox x
End Sub
 
B

Bill

Thanks a lot.

Bill

Dave Peterson said:
I used this version of your code:

Option Explicit
Sub testme()

Dim varFormat As String
Dim x As Variant
Dim xbar As Variant

MsgBox ActiveCell.NumberFormat
varFormat = ActiveCell.NumberFormat
xbar = 0
x = Format(xbar, varFormat)
MsgBox x
End Sub

But there's a difference between the worksheet function =Text() and the VBA
function Format.

Option Explicit
Sub testme2()

Dim varFormat As String
Dim x As Variant
Dim xbar As Variant

MsgBox ActiveCell.NumberFormat
varFormat = ActiveCell.NumberFormat
xbar = 0
x = Application.Text(xbar, varFormat)
'or if you know it's General in the worksheet function
'x = format(xbar,"general number")
MsgBox x
End Sub
Click to expand...
 
Top