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

[leeman]

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>

 

[davesweep]

This should get you started:

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]

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

 

[leeman]

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>

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></pre>