Macro oving minus sign from right to left not perfect yet

  • Thread starter Thread starter Ralf
  • Start date Start date
R

Ralf

Hope somebody can help me. I copied the lines listed below
into a macro to move the minus sign from data imported from a different
source from the tight to the left.
The macro works, however, the results are rounded and the decimals are not
taken into account. When I enter f.e. 100,23- and run the macro the result
is -100
which is not very handy since I use it a lot on journal entries.



Sub ConvertMinus()
Dim MemberCell As Range
For Each MemberCell In ActiveSheet.UsedRange
If Right(MemberCell.Formula, 1) = "-" Then
MemberCell.Formula = _
Val("-" & Val(Left(MemberCell.Formula,
Len(MemberCell.Formula) - 1))) 'Formula for moving sign
MemberCell.NumberFormat = "#,##0.00_);-#,##0.00" 'Controls
number format
Else
End If
Next
End Sub



Thanks in advance.

Ralf
 
Hi Ralf

Just multiply the cell content by one and let Excel fix the rest herself.
(You have to select the cells in question before you run this demo):

Sub test()
Dim Cel As Range
For Each Cel In Selection
If Cel.HasFormula = False Then
If IsNumeric(Cel.Value) Then
Cel.Value = Cel.Value * 1
End If
End If
Next
End Sub

But are you sure your original code return no decimals ? It seem to do "hide
decimals", not delete them. Click a cell and see what the formula bar really
contains. Excel is not wysiwyg.

HTH. Best wishes Harald
 
Harald,

I tried the original maco once more, however formatting cells, unhiding etc.
did not work, as fas as I can see it simply rounds the results.
The good news is that your macro works perfect. Thanks a lot.


Regards,
Ralf
 
Back
Top