commas missing at copy and past in excel

[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

 

[Gary Keramidas]

can you post the code you use to copy, you need to make sure you're copying the
formats, too

 

[Guest]

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

 

[Norman Jones]

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

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

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

 

[Norman Jones]

Hi Michael,

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

Should read:

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

 

[Guest]

Norman,

Thank you very much, that worked perfectly.

Best regards

Michael

Hi Michael,

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

Should read:

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