commas missing at copy and past in excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a macro, part of which I convert a column of figures to currency with
zero decimal places, a euro symbol and with comma for the 000s.
When I then copy these cells to another column, it puts back the 2 decimal
places and loses the commas and the euro. As the cell is now part of a
collection of cells and mixed with regular text I can't isolate it and reset
it to currency again.
How do I make it hold the currency values when I copy? I have tried
selecting the cells and copying values but it still seems to lose the stuff.

Any help would be appreciated.

Michael
 
can you post the code you use to copy, you need to make sure you're copying the
formats, too
 
Here is the code I am using

Columns("G:G").Select
Selection.NumberFormat = "€#,##0"

Columns("G:G").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=PROPER(RC[-11]&"", ""&RC[-10]&"" ""&RC[-9]&"" ""&RC[-8]&"".
€""&RC[-5]&""^"")"

' Range("L1").Select
' Selection.Copy
' Selection.AutoFill Destination:=Range("L:L"), Type:=xlFillDefault
 
Hi Michael,

Try something like:

'=============>>
Public Sub Tester()
Dim LRow As Long
Const col As String = "A" '<<==== CHANGE

LRow = Cells(Rows.Count, col).End(xlUp).Row

With Range("G1:G" & LRow)
.NumberFormat = "?#,##0"
.Value = .Value
End With

With Range("L1")
.FormulaR1C1 = _
"=PROPER(RC[-11]& "", "" & RC[-10]& "" ""& RC[-9]&" & _
" "" ""& RC[-8]& "".?"" &" _
& " text(RC[-5],""0"")&""^"")"
.Copy
.AutoFill Destination:=Range("L1:L" & LRow), _
Type:=xlFillDefault
End With

End Sub
'<<=============


---
Regards,
Norman



MGM said:
Here is the code I am using

Columns("G:G").Select
Selection.NumberFormat = "?#,##0"

Columns("G:G").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=PROPER(RC[-11]&"", ""&RC[-10]&"" ""&RC[-9]&"" ""&RC[-8]&"".
?""&RC[-5]&""^"")"

' Range("L1").Select
' Selection.Copy
' Selection.AutoFill Destination:=Range("L:L"), Type:=xlFillDefault


MGM said:
I have a macro, part of which I convert a column of figures to currency
with
zero decimal places, a euro symbol and with comma for the 000s.
When I then copy these cells to another column, it puts back the 2
decimal
places and loses the commas and the euro. As the cell is now part of a
collection of cells and mixed with regular text I can't isolate it and
reset
it to currency again.
How do I make it hold the currency values when I copy? I have tried
selecting the cells and copying values but it still seems to lose the
stuff.

Any help would be appreciated.

Michael
Click to expand...
Click to expand...
 
Hi Michael,
"=PROPER(RC[-11]& "", "" & RC[-10]& "" ""& RC[-9]&" & _
" "" ""& RC[-8]& "".?"" &" _
& " text(RC[-5],""0"")&""^"")"
Click to expand...

Should read:

"=PROPER(RC[-11]& "", "" & RC[-10]& "" ""& RC[-9]&" & _
" "" ""& RC[-8]& "".?"" &" _
& " text(RC[-5],""#,##0"")&""^"")"
 
Norman,

Thank you very much, that worked perfectly.

Best regards

Michael

Norman Jones said:
Hi Michael,
"=PROPER(RC[-11]& "", "" & RC[-10]& "" ""& RC[-9]&" & _
" "" ""& RC[-8]& "".?"" &" _
& " text(RC[-5],""0"")&""^"")"
Click to expand...

Should read:

"=PROPER(RC[-11]& "", "" & RC[-10]& "" ""& RC[-9]&" & _
" "" ""& RC[-8]& "".?"" &" _
& " text(RC[-5],""#,##0"")&""^"")"
Click to expand...
 
Back
Top