Help Number formatting of actual data not just the view

[JC]

need to format from range E4 to E2003
numbers to be four characters if only three characters
add a zero and a ' for hyperion to recognize any ideas anyone what I had

Range("E4").Select
Range(Selection, Selection.End(xlDown)).Select

Z = Selection.Address 'get the address
For Each X In ActiveSheet.Range(Z) 'Do while
If Len(X) > 0 Then 'Find cells with data
X.FormulaR1C1 = Chr(39) & X.Text '39 is code for tick
Else
X.FormulaR1C1 = "" 'If empty do not put tick

End If

takes too long to format help

 

[Jim Cone]

Not tested...
'--
Const strStart As String = "'"
Const strFiller As String = "'0"
Range("E4").Select
Range(Selection, Selection.End(xlDown)).Select

z = Selection.Address
For Each x In ActiveSheet.Range(z).Cells
If Len(x.Value) = 3 Then
x.Value = strFiller & x.Value
ElseIf Len(x.Value) = 4 Then
x.Value = strStart & x.Value
End If
Next 'x
'--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"JC"
wrote in message
need to format from range E4 to E2003
numbers to be four characters if only three characters
add a zero and a ' for hyperion to recognize any ideas anyone what I had

Range("E4").Select
Range(Selection, Selection.End(xlDown)).Select

Z = Selection.Address 'get the address
For Each X In ActiveSheet.Range(Z) 'Do while
If Len(X) > 0 Then 'Find cells with data
X.FormulaR1C1 = Chr(39) & X.Text '39 is code for tick
Else
X.FormulaR1C1 = "" 'If empty do not put tick

End If

takes too long to format help

 

[Rick Rothstein \(MVP - VB\)]

Also untested.... does this do what you want?

For Each x In ActiveSheet.Range("E4:E" & _
ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row)
If Len(x.Text) = 3 Then
x.Text = "'0" & Replace(x.Text, "'", "")
ElseIf Len(x.Text) = 4 Then
x.Text = "'" & Replace(x.Text, "'", "")
Else
x.Text = ""
End If
Next

Rick

 

[julie cooke]

Does the Zero need to be a leading Zero? Do they need to be treated as
numbers for calculation purposes or text (ie for coding)

You could probably use a custom format to ensure that all cells are 4
numbers wide.

I came across this problem many years ago and we were forced to use Lotus
123 as it allowed the download to be shown as text rather that a number. I
would guess that updates within Excel would've now caught up.

Let me know how you get on.