Number stored as text

[stevepctamers]

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 remainnumbers stored as text!

Any help gratefully received.

TIA

Steve

Steve_LA1

 

[Auric__]

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.

 

[stevepctamers]

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

 

[stevepctamers]

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

 

[Auric__]

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