PDA

View Full Version : Copy and Paste Problem



buddy2000
03-10-2011, 11:32 AM
When I copy and pasted this data into excel. It must have made all the numbers labels. Now I can not create formulas. I thought all the numbers where text and I tried converting them by multiplying by one. It has not worked so I concluded that the numbers must be labels. I can not find any information on how to convert them.


This is really not a VBA question. However I know that if you know VBA you will no how to fix this.

Thanks in Advise for helping. It will be really helpful to know how to fix this problem in the future.

Kenneth Hobs
03-10-2011, 12:12 PM
You have trailing space characters so they have to be removed. e.g.
Sub Trim()
Dim cell As Range
For Each cell In Selection
cell.Value2 = Trim(cell.Value2)
cell.NumberFormat = "#,##0"
Next cell
End Sub

buddy2000
03-10-2011, 01:08 PM
That does not seem to work. I get an error. Wrong number of arguements.

buddy2000
03-10-2011, 01:18 PM
Ok I ran this macro an the code was executed. However, the numbers in the cell must be a label. I still can not create formulas.

Sub CleanSheet1()
Dim Cell As Range
For Each Cell In Sheet1.UsedRange
Cell.Value = Trim(Cell)
Next Cell
End Sub

Aussiebear
03-10-2011, 03:18 PM
I am able to construct formulas within your spreadsheet on my system. When you copied and pasted, did you use paste special or just paste? Please note that the code you supplied only works on sheet 1 ("Income Statement") of the workbook, so you will need to adjust this to work on all of the sheets.