Help!

I have some code that copies in data from one s/sheet to a summary workbook.. The problem is that it is storing one of the columns of numbers as text.

I've ensured the cells in the original data are formatted as numbers. I've also tried several versions of "Paste Values" etc. If I do all the usual things "manually" (the "usual" being copy a cell with zero in it & paste special onto the recalcitrant cells and choose 'Add'; click the XL warning and convert to numbers; data Text-to-columns and choose General) the cells get successfully stored as numbers.



However, if I do these through a macro none of them work - the cells remain numbers stored as text!

Any help gratefully received.

TIA

Steve

Steve_LA1

stevepctamers wrote:

I have some code that copies in data from one s/sheet to a summary
workbook. The problem is that it is storing one of the columns of
numbers as text.

I've ensured the cells in the original data are formatted as numbers.
I've also tried several versions of "Paste Values" etc. If I do all the
usual things "manually" (the "usual" being copy a cell with zero in it &
paste special onto the recalcitrant cells and choose 'Add'; click the XL
warning and convert to numbers; data Text-to-columns and choose General)
the cells get successfully stored as numbers.



However, if I do these through a macro none of them work - the cells
remain numbers stored as text!

Post your code.

--
I had no idea what a computer was truly capable of. A computer was this
all-powerful glowing artifact of power that held unlimited potential.
Of course now I'm a computer programmer and have a much different view
of the evil box of unlimited GPF's.

Hi Auric__

Thanks for the fast response. Here's the code . . . the place where it's going wrong is where it selects Data3 and does a paste special

I've tried variations on Paste Specials etc and also simple "Paste". If I step thru the code, as soon as it performs the Paste Special the figures change to numbers stored as text!!

Steve

Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next

Workbooks.Open Filename:=FILNAME_Open

' Windows(FILNAME2_Open & ".xlsm").Activate

' Unhides then selects Sheet3

Sheets("Sheet3").Visible = True
Sheets("Sheet3").Select

ActiveSheet.Range("$B$2:$H$2").AutoFilter Field:=3, Criteria1:="<"
Range("E5").Select
ActiveSheet.Range("$B$2:$H$2").AutoFilter Field:=5, Criteria1:="<0.00%"

Range("B3").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy

Sheets("Sheet3").Visible = False

' closes the file we copied the data from
ActiveWorkbook.Close True

' Windows("Summary Time Tracker v1p.xlsm").Activate
Sheets("Data3").Select

Range("C2").Select
ActiveSheet.Range("C65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Select

ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True

' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' copies down vlookup for month name, from the cell above
ActiveSheet.Range("B65536").End(xlUp).Select

Dim endRow As Long
endRow = Cells(Rows.Count, "C").End(xlUp).Row 'this is the column to look into, to see how far down to fill
ActiveCell.AutoFill Destination:=Range(ActiveCell.Address & ":B" & endRow) ' this is the column to fill down


' formats col G for %s

Range("G3:G5000").Style = "Percent"
Selection.NumberFormat = "0.00%"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With


Range("B2").Select


Sheets("Processing").Select
Range("A1").Select

' sets up project names for "Money Chart"
Sheets("Data3").Select
Range("E3:E198").Select
Selection.Copy

Sheets("Inputs").Select
Range("B54").Select
Sheets("Data3").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("B55:B250").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Selection.Copy

Sheets("OPX per project").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A11").Select


Sheets("Processing").Select
Sheets("Processing").Activate
Range("A1").Select



On Friday, March 1, 2013 12:35:14 PM UTC, Auric__ wrote:
stevepctamers wrote:



I have some code that copies in data from one s/sheet to a summary

workbook. The problem is that it is storing one of the columns of

numbers as text.



I've ensured the cells in the original data are formatted as numbers.

I've also tried several versions of "Paste Values" etc. If I do all the

usual things "manually" (the "usual" being copy a cell with zero in it &

paste special onto the recalcitrant cells and choose 'Add'; click the XL

warning and convert to numbers; data Text-to-columns and choose General)

the cells get successfully stored as numbers.







However, if I do these through a macro none of them work - the cells

remain numbers stored as text!



Post your code.



--

I had no idea what a computer was truly capable of. A computer was this

all-powerful glowing artifact of power that held unlimited potential.

Of course now I'm a computer programmer and have a much different view

of the evil box of unlimited GPF's.

Solved! Aaaaaaggghhhhh! Solvd this myself . . .

This was due to the source data being formatted as currency. The Paste values was pasting it with the currency symbol, hence it was formatted as text in the summary document.

So, I changed the format in the source doc to be numbers (not currency) and Hey! Presto! it works a treat.

Steve

stevepctamers wrote:

Solved! Aaaaaaggghhhhh! Solvd this myself . . .

This was due to the source data being formatted as currency. The Paste
values was pasting it with the currency symbol, hence it was formatted
as text in the summary document.

So, I changed the format in the source doc to be numbers (not currency)
and Hey! Presto! it works a treat.

Good to hear.

In the future, it might be worth it to copy the cells to variants and convert
explicitly, e.g.:

Dim cell As Range, tmp As Variant
For Each cell In Selection
tmp = CCur(cell.Value)
'etc.
targetCell.Value = tmp
Next

--
The Stones, I love the Stones. I watch them whenever I can. Fred, Barney...
-- Steven Wright