Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > macro to look down cells and if negative in the right of the cell change to left of the cell
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 53
Default macro to look down cells and if negative in the right of the cell change to left of the cell
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default macro to look down cells and if negative in the right of the cell
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

" wrote:

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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 53
Default macro to look down cells and if negative in the right of the cell
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

On 27 Jul, 13:10, Joel wrote:
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



" wrote:
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- Hide quoted text -

- Show quoted text -


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default macro to look down cells and if negative in the right of the c
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

" wrote:

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

On 27 Jul, 13:10, Joel wrote:
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



" wrote:
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- Hide quoted text -

- Show quoted text -



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 53
Default macro to look down cells and if negative in the right of the c
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

On 29 Jul, 13:56, Joel wrote:
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



" wrote:
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

On 27 Jul, 13:10, Joel wrote:
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

" wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default macro to look down cells and if negative in the right of the c
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.

" wrote:

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

On 29 Jul, 13:56, Joel wrote:
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



" wrote:
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

On 27 Jul, 13:10, Joel wrote:
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

" wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default macro to look down cells and if negative in the right of the c
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)



" wrote:

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

On 29 Jul, 13:56, Joel wrote:
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



" wrote:
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

On 27 Jul, 13:10, Joel wrote:
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

" wrote:
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

--

Dave Peterson
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Change cell value from positive to negative deniseh Excel Discussion (Misc queries) 4 July 16th 07 04:30 PM
HOW TO CHANGE CELL STARTING FROM RIGHT TO LEFT pamwp Excel Discussion (Misc queries) 1 January 9th 07 11:10 PM
change cell from negative to positive Terry Excel Discussion (Misc queries) 1 July 15th 05 07:15 PM
MACRO: How to change cells to negative value Tony Excel Programming 2 July 8th 04 11:49 PM
Please help! Macro to change cell contents based on cell to the left Jennifer[_8_] Excel Programming 7 March 4th 04 01:06 AM


All times are GMT +1. The time now is 05:27 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement