Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to look down cells and if negative in the right of the c
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" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to look down cells and if negative in the right of the c
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 wrote: 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" wrote: 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 -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to look down cells and if negative in the right of the c
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) "Dave Peterson" wrote: 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 wrote: 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" wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change cell value from positive to negative | Excel Discussion (Misc queries) | |||
HOW TO CHANGE CELL STARTING FROM RIGHT TO LEFT | Excel Discussion (Misc queries) | |||
change cell from negative to positive | Excel Discussion (Misc queries) | |||
MACRO: How to change cells to negative value | Excel Programming | |||
Please help! Macro to change cell contents based on cell to the left | Excel Programming |