macro to look down cells and if negative in the right of the cell change to left of the cell

[amorrison2006]

Hi

I have a column of data.

Unfortunately the negative symbol is on the right of the numbers to
represent a negative number.

What I need is a macro to look down a column and if it find the first
character to the right of that cell is a "-" symbol then move that to
the first character on the left of the cell to make the numbers
negative.

I hope this is possible and someone can help,

Many thanks,

Stuart

 

[Joel]

Sould of asked yesterday.

Sub movetofront()
ActiveSheet.Activate
LastRow = Cells(Rows.Count, ActiveCell.Column). _
End(xlUp).Row
Set ColumnRange = Range(Cells(1, ActiveCell.Column), _
Cells(LastRow, ActiveCell.Column))

For Each cell In ColumnRange

If (Not IsEmpty(cell)) And (cell.Text <> "") Then
If InStr(cell, "-") > 0 Then
If Right(cell, 1) = "-" Then
cell.Value = -1 * Val(cell)
End If
End If
End If
Next cell

End Sub

 

[amorrison2006]

Hi Joel,

This macro did not work,

The numbers in my column have commas in place and are not formatted
numbers obviously because the negative is the wrong way round.

The macro cut off all the number after the comma but did put the
negative in the right place,

Can this be corrected?

I appreciate your help,

Andrea

 

[Joel]

the macro worked, it just you didn't give me enough info to do the job right.
somebody else had a similar posting with the negative sign on the right side
a similar code worked. sorry for the problem, easily fixed

Sub movetofront()
ActiveSheet.Activate
LastRow = Cells(Rows.Count, ActiveCell.Column). _
End(xlUp).Row
Set ColumnRange = Range(Cells(1, ActiveCell.Column), _
Cells(LastRow, ActiveCell.Column))

For Each cell In ColumnRange

If (Not IsEmpty(cell)) And (cell.Text <> "") Then
If InStr(cell, "-") > 0 Then
If Right(cell, 1) = "-" Then
cell.Value = "-1" & Left(cell, Len(cell) - 1)
End If
End If
End If
Next cell

End Sub

 

[amorrison2006]

Hi Joel

This is the first time I have managed to try this again,

It still doesnt work.

It turns,

132.50- into -1132.50

Can this be amended again?

Please

Thanks so much for your input,

Andrea

 

[Joel]

Boy am I stupid. The fix is easy. You can't multiple a string by a number!


from:
cell.Value = "-1" & Left(cell, Len(cell) - 1)
to:
cell.Value = "-" & Left(cell, Len(cell) - 1)

I was putting the extra one into the string.

 

[Dave Peterson]

I think it was a typo in the code.

Try changing this line:

cell.Value = "-1" & Left(cell, Len(cell) - 1)
to

cell.Value = -1 * Left(cell, Len(cell) - 1)

 

[Joel]

Dave: Amorrison asked in previous posting for it to be a string after a
originally made it a number. When I changed it from your solution (earlier
posting) to my latest solution I forgot to remove the 1 from the string.

 

[Dave Peterson]

I didn't read the earlier posts.

But are you sure your code kept the values strings?

In my simple test, the strings: 1,234,567.891-
were converted to real negative numbers with your code.

This kept it text for me:

Option Explicit
Sub movetofront()
Dim LastRow As Long
Dim ColumnRange As Range
Dim Cell As Range

LastRow = Cells(Rows.Count, ActiveCell.Column). _
End(xlUp).Row
Set ColumnRange = Range(Cells(1, ActiveCell.Column), _
Cells(LastRow, ActiveCell.Column))

For Each Cell In ColumnRange
If (Not IsEmpty(Cell)) And (Cell.Text <> "") Then
If InStr(Cell, "-") > 0 Then
If Right(Cell, 1) = "-" Then
With Cell
.NumberFormat = "@" 'text
.Value = "-" & Left(Cell, Len(Cell) - 1)
End With
End If
End If
End If
Next Cell

End Sub

====
If I were converting those trailing negative strings to negative numbers, I'd
use something like:

Option Explicit
Sub testme()
Dim myCell As Range
For Each myCell In Selection.Cells
If Right(myCell.Value, 1) = "-" Then
myCell.Value = CDbl(myCell.Value)
End If
Next myCell
End Sub

 

[Joel]

The easy way to solve tthe problem is to put a single quote in front of the
string. Notice the code below has a double quote followed by a single quote.
Excel has a mind of its own when its changes strings to numbers for no
reason at all.

from:
cell.Value = "-" & Left(cell, Len(cell) - 1)
to:
cell.Value = "'-" & Left(cell, Len(cell) - 1)