Userdefined Function / Formulae

JayModi

New Member
Joined
Jul 5, 2010
Messages
8
Dear Excel Experts-

We use SAP system and whenever I copy any data (from SAP) and paste it to excel, it paste it in following format (which is incompatible to sum):

1,468.98
29,463.83
2,281.10-
12,684.40-
64,254.04-

So this causes us manually to change it to:

1,468.98
29,463.83
-2,281.10
-12,684.40
-64,254.04

Is there a way I can write a formulae or function in VBA which will automatically change the 64,254.04- to -64,254.04 and leave the postive ones as it is? If there is something like that than I would really apprecite if you can provide the VBA code (procedure).

Thank You in advance.
Jay
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Tinbendr

Well-known Member
Joined
Jul 21, 2010
Messages
997
We use SAP system
My condolences.

Try this.
Code:
Sub ChangeSigns()
Dim A As Long
Dim ColNum As Long
'Cursor must be in column in question.
ColNum = ActiveCell.Column

With ActiveSheet
    LastRow = .Cells(.Rows.Count, ColNum).End(xlUp).Row
    For A = 1 To LastRow
        If Right(.Cells(A, ColNum).Value, 1) = "-" Then
            .Cells(A, ColNum).Value = _
                Left(.Cells(A, ColNum).Value, _
                Len(.Cells(A, ColNum).Value) - 1) * -1
        End If
    Next
End With
End Sub
My ZAP doesn't produce any negative numbers in my work, but assuming they get translated as text, this should work.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,752
Office Version
2010
Platform
Windows
You can also select the column and do Data > Text to columns, Finish.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,689
Messages
5,488,273
Members
407,633
Latest member
varunwalla

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top