First, changing the format simply changes the appearance of a numeric value. It does not change the actual value. It is unclear whether that would meet Ryan's needs. (Ryan, what say you?)
JoeU2004, the *appearance* is currently fine, I need to make sure that when you click on the cell, there are no places after the decimal.
I don't think we are using the terminology in the same way. I'll try to be less subtle. Please answer all of the following questions,
highlighted in red.
Suppose A1 initially displays the 20-digit "number" 52.945849949543435656. After we copy A1 to a new worksheet, if we enter the formula =A1*100 into B1 in the new worksheet, which result do you want to see in B1: 5300 or 5295, assuming B1 is formatted as Number with 0 decimal places?
If you want to see 5300 in B1. you want the
actual value in A1 to be 53. Of course, that is
also how it will appear.
On the other hand, if you want to see 5295 in B1, you want the actual value in A1 to be the original 20-digit number (to the extent possible),
only changing its
appearance to be 53. That is, formatting it as Number with 0 decimal places.
The difference is: "
also appearing" v. "
only appearing".
-----
In any case, it is
not necessary to explicitly do Workbooks.Add and ActiveSheet.Paste.
And it is
not sufficient to change the numeric format of the copied cells.
Instead of:
Rich (BB code):
Cells.Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
it is sufficient and probably a lot more efficient to write:
That statement copies ActiveSheet, creates a new workbook, and effectively pastes into Sheet1 of the new workbook.
-----
But if A1 displays a 20-digit "number" like 52.945849949543435656, the "number"
must be text, not truly numeric.
Please confirm: does =ISTEXT(A1) return TRUE?
Any numeric format -- for example, Number with 0 decimal places, which is the same range.NumberFormat="0" in VBA -- has
no effect on text in the cell, even if it looks numeric.
So after doing ActiveSheet.Copy, we must physically change some cells.
But I confess: I no longer trust my understanding of exactly what cells you want to change.
Do all the cells in the copied worksheet contain "numeric" text like your 20-digit example?
Do any cells contain other data that you do not want to change; perhaps other text, or real numeric values, or formulas?
If they contain formulas that return real numeric values or "numeric" text, do you want to replace those formulas with their current value?
It would be ideal if you would upload an example Excel file to a file-sharing website and post the "shared" URL in a response to this thread.
The worksheet in the example Excel file should be representative of the variety of data that you expect in the real worksheet to be copied.
-----
Does one of the following code fragments (which?) do what you want? If not, what does it do differently, and how should it behave?
If you want to change the actual value as well as appearance:
Rich (BB code):
Dim v As Variant, nr As Long, nc As Long
Dim rng As Range, i As Long, j As Long
ActiveSheet.Copy ' into Sheet1 of new workbook
Set rng = ActiveSheet.UsedRange
If rng.Count = 1 Then
ReDim v(1 To 1, 1 To 1) As Variant
v(1, 1) = rng
Else
v = rng
End If
nr = UBound(v, 1) ' number of used rows
nc = UBound(v, 2) ' number of used columns
For i = 1 To nr: For j = 1 To nc
If v(i, j) <> "" Then
If IsNumeric(v(i, j)) Then
With rng(i, j)
.NumberFormat = "0"
.Value = WorksheetFunction.Round(v(i, j), 0)
End With
End If
End If
Next j, i
If you want to change only the appearance, which requires that you also change "numeric" text to real numeric values:
Rich (BB code):
Dim v As Variant, nr As Long, nc As Long
Dim rng As Range, i As Long, j As Long
ActiveSheet.Copy ' into Sheet1 of new workbook
Set rng = ActiveSheet.UsedRange
If rng.Count = 1 Then
ReDim v(1 To 1, 1 To 1) As Variant
v(1, 1) = rng
Else
v = rng
End If
nr = UBound(v, 1) ' number of used rows
nc = UBound(v, 2) ' number of used columns
For i = 1 To nr: For j = 1 To nc
If v(i, j) <> "" Then
If IsNumeric(v(i, j)) Then
With rng(i, j)
.NumberFormat = "0"
.Value = CDbl(v(i, j))
End With
End If
End If
Next j, i
Is any this helpful?
