Chipley
02-25-2008, 02:39 AM
Hi,
The script below is returning unwanted values.
Sub ValuesToString()
Dim i As Integer
'This will ensure that excel doesn't display
'what it is doing - a bit of performance increase
'for large Excel files
Application.ScreenUpdating = False
'Loop thru all sheets in a workbook
For i = 1 To ActiveWorkbook.Sheets.Count
'Selects all cells in a worksheet
Sheets(i).Cells.Copy
'Paste the copied cells as values instead of formulas
Sheets(i).Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'TODO: Paste special with custom cell formatting
Sheets(i).Cells.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'==================================================
Next i
Application.ScreenUpdating = True
End Sub
I'm trying to modify this script to do the following:
For instance, a cell has this formatting:
The value of cell A1 = Q0.0, A2 = Q0.1, A3 = Q0.2, and so on. These values are created through the Custom cell formatting.
Right-click a cell>Format Cells>Number tab>Custom
The custom formatting for these cells is "Q"0".0". However, when i run the ValuesToStrings function, the custom formatting is lost and all that is displayed in the formula field for a given cell when selected is 0. A1 = 0, A2 = 0, A3 = 0, and so on. The values in the Excel cells look correct, but the formula field needs to be the same as the cell values.
What I would like to see in the formula field when A1, A2, or A3 is selected is Q0.0, Q0.1, or Q0.2 ... , but instead, the formula field is displaying 0 for all 3 cells if that makes any sense. This plays an important role for the next step of this prototype project, which uses a java converter, but that's another story, and not relevant here.
Hope that makes sense.
The script below is returning unwanted values.
Sub ValuesToString()
Dim i As Integer
'This will ensure that excel doesn't display
'what it is doing - a bit of performance increase
'for large Excel files
Application.ScreenUpdating = False
'Loop thru all sheets in a workbook
For i = 1 To ActiveWorkbook.Sheets.Count
'Selects all cells in a worksheet
Sheets(i).Cells.Copy
'Paste the copied cells as values instead of formulas
Sheets(i).Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'TODO: Paste special with custom cell formatting
Sheets(i).Cells.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'==================================================
Next i
Application.ScreenUpdating = True
End Sub
I'm trying to modify this script to do the following:
For instance, a cell has this formatting:
The value of cell A1 = Q0.0, A2 = Q0.1, A3 = Q0.2, and so on. These values are created through the Custom cell formatting.
Right-click a cell>Format Cells>Number tab>Custom
The custom formatting for these cells is "Q"0".0". However, when i run the ValuesToStrings function, the custom formatting is lost and all that is displayed in the formula field for a given cell when selected is 0. A1 = 0, A2 = 0, A3 = 0, and so on. The values in the Excel cells look correct, but the formula field needs to be the same as the cell values.
What I would like to see in the formula field when A1, A2, or A3 is selected is Q0.0, Q0.1, or Q0.2 ... , but instead, the formula field is displaying 0 for all 3 cells if that makes any sense. This plays an important role for the next step of this prototype project, which uses a java converter, but that's another story, and not relevant here.
Hope that makes sense.