Macros

[cheri gemini]

When pasting data from our main frame, negative numbers have the - sign at
the end. I need a macro to edit the cell, backspace, home, - enter. Any
ideas?

 

[Tom Hutchins]

Here is Gord Dibben's reply to a similar post:

Select the range.
Data>Text to Columns>Next>Next>Advanced. Checkmark in "trailing minus sign for
negative numbers">Finish. (This feature appeared with Excel 2002)

A macro if you wish.

Sub Negsignleft()
Dim cell As Range
Dim rng As Range
''move minus sign from right to left on entire worksheet
On Error Resume Next
Set rng = ActiveSheet.Cells. _
SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0
For Each cell In rng
If IsNumeric(cell.Value) Then
cell.Value = CDbl(cell.Value)
End If
Next cell
End Sub

Put the macro code in a general VBA module in your workbook. If you are new
to macros, this link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

Hope this helps,

Hutch

 

[FSt1]

hi
another way
since you didn't say, i assumed that your numbers would be in a column. i
chose column F for test purposes only. adjust to suit.
Sub changeit()
Dim r As Range
Dim lr As Long
lr = Cells(Rows.Count, "f").End(xlUp).Row
Set r = Range("F2:F" & lr)
For Each cell In r
'cell.Select 'not needed. test pruposes only.
cell.Value = "-" & Left(cell, Len(cell) - 1)
Next cell

End Sub

regards
FSt1