Expert: Bill - 12/2/2005

Question
-------------------------
Followup To
Question -
-------------------------
Followup To
Question -
-------------------------
Followup To
Question -
Hi Bill,

Our General Ledger software displays the negative sign at the end of the number and Excel does not recognize this as a number. How would I move the negative sign to the front of the number?

Thanks
Answer -
Go to the cell where the number is you want to change and edit the cell.  Delete the negative sign at the end of the cell's contents, type a negative sign at the beginning of the cell contents, and then press enter.


Hi Bill,

That part I knew.  I should have been more specific. Is there a macro that would automate this? It would make General Ledger analysis a lot more easier.

Thanks
Answer -
There is no macro that exists that will do this or practically any other task - you have to write them yourself or get someone to write them for you.  Here is a formula that will do what you want.  It tests A1

=IF(RIGHT(TRIM(A1),1)="-",LEFT(TRIM(A1),LEN(TRIM(A1))-1)*-1,A1)

A macro will need to loop thru ALL cells in the sheet where the data is and do the conversion.  Since I do not know where the data is and/or how large of an area it covers (you did not say) then here is a generic macro that covers only A1:B10


Dim X As Range
Dim Y As Range

Set Y = Range("A1:B10")

For Each X In Y
If X <> "" Then
If Right(Trim(X), 1) = "-" Then
X = Left(Trim(X), Len(Trim(X)) - 1) * -1
End If
End If
Next X


Hi Bill,

When I execute the above macro, the VBE Editor displays an error - "Invalid Outside Procedure".  What would be the fix?

Thanks
Answer -
There is nothing wrong the code.  What did you name the macro you put the code in I sent and where did you put the macro?

I did not name it anything. I just copied it into a blank module in VBE. When I executed it, it displayed the error.

Answer
<sigh>  I assumed you knew something about macros.  Again, you did not say.  All I have to go is what you say - nothing more, nothing less.

Copy this to the VBE

Sub FixNegativeSigns()
Dim X As Range
Dim Y As Range

Set Y = Range("A1:B10")

For Each X In Y
If X <> "" Then
If Right(Trim(X), 1) = "-" Then
X = Left(Trim(X), Len(Trim(X)) - 1) * -1
End If
End If
Next X
End Sub