Search range, Find character, replace and multiply to get negative

leeman

New Member
Joined
Feb 9, 2010
Messages
11
I have a worksheet from a customer which include negative numbers however the negative numbers have the negative sign at the back.

These are three columns as an example..


<code>ADJUSTMENTS_$ OTHER_CHARGES_$ TOTAL_$_EXCL_GST 48131.75- 840.97 37560.92- 48131.75- 1140.92 36230.24- 48131.75 1036.25 37053.90 24216.95 517.04 18357.23 24216.95- 701.6 17468.25-

</code>I wish to develop a macro to find the cells with this negative number in the worksheet. So I need to find cells with "-" sign at the end , for these cells I need to remove the "-", and then multiply the number to get into correct negative format.<code>

</code><code>ADJUSTMENTS_$ OTHER_CHARGES_$ TOTAL_$_EXCL_GST -48131.75 840.97 -37560.92 -48131.75 1140.92 -36230.24 48131.75 1036.25 37053.90 24216.95 517.04 18357.23 -24216.95 701.6 -17468.25

my worded plan is this....

Open workbook
look on first sheet 'data is only on first sheet
find any cell with "-" at the end of the string
</code><code>For all cells with </code>that are found
<code>replace this "-" with "" and then multiply by that cell by -1
do for all cells (loop required)
Save worksheet
</code><code>


</code></pre>
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

davesweep

Well-known Member
Joined
Apr 30, 2007
Messages
510
This should get you started:

Code:
Sub CorrectNegatives()
Dim rWorkingRange As Range, rCell As Range

Set rWorkingRange = ActiveSheet.UsedRange

For Each rCell In rWorkingRange
If Right(rCell, 1) = "-" Then rCell = "-" & Left(rCell, Len(rCell) - 1)
Next rCell

End Sub
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,079
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I guess I prefer davesweep's macro but i decided to post mine .

Sub Removesign()
Dim I As Integer
Dim j As Integer
Dim lastrow As Integer
Dim lastcolumn As Integer
lastrow = Range("A1").End(xlDown).Row
lastcolumn = Range("A1").End(xlToRight).Column​
For I = 2 To lastrow​
For j = 1 To lastcolumn​
If Right(Cells(I, j), 1) = "-" Then
Cells(I, j).Value = -1 * Left(Cells(I, j).Value, Len(Cells(I, j).Value) - 1)​
End If​
Next j​
Next I
End Sub
 
Last edited:

leeman

New Member
Joined
Feb 9, 2010
Messages
11
I guess I prefer davesweep's macro but i decided to post mine .

Sub Removesign()
Dim I As Integer
Dim j As Integer
Dim lastrow As Integer
Dim lastcolumn As Integer
lastrow = Range("A1").End(xlDown).Row
lastcolumn = Range("A1").End(xlToRight).Column​
For I = 2 To lastrow​
For j = 1 To lastcolumn​
If Right(Cells(I, j), 1) = "-" Then
Cells(I, j).Value = -1 * Left(Cells(I, j).Value, Len(Cells(I, j).Value) - 1)​
End If​
Next j​
Next I
End Sub

Thanks to the both of you. They both work great :). I also found on anotehr site yet a diffferent variation. Appreciate your assistance and this post is SOLVED :)

<code>
Code:
Private Sub Workbook_Open() Dim C As Long Dim Data As Variant Dim R As Long Data = Sheet1.UsedRange.Value For R = 1 To UBound(Data, 1) For C = 1 To UBound(Data, 2) If Right(Data(R, C), 1) = "-" Then Data(R, C) = Val(Data(R, C)) * -1 End If Next C Next R Sheet1.UsedRange.Value = Data End Sub
Code:

</code></pre>
 

Watch MrExcel Video

Forum statistics

Threads
1,129,809
Messages
5,638,482
Members
417,027
Latest member
wlknspc7

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top