commas missing at copy and past in excel
 

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

commas missing at copy and past in excel
 

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

--


Gary


"MGM" wrote in message
...
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

commas missing at copy and past in excel
 

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" wrote:

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

commas missing at copy and past in excel
 

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" wrote in message
...
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" wrote:

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

commas missing at copy and past in excel
 

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"")&""^"")"

---
Regards,
Norman

commas missing at copy and past in excel
 

Norman,

Thank you very much, that worked perfectly.

Best regards

Michael

"Norman Jones" wrote:

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"")&""^"")"

---
Regards,
Norman