Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a macro/formula
Perhaps this is a silly question, but i just can't figure out how to format this worksheet. The problem is that a lot of the cells have numbers in them with negative signs in the back instead of in the front (ie. 23-) and i was wondering if anyone knew of a macro/formula i could use to quickly fix this problem for all the cells. I would be extremely grateful. Thanks!!! -- brefed15 ------------------------------------------------------------------------ brefed15's Profile: http://www.excelforum.com/member.php...o&userid=35573 View this thread: http://www.excelforum.com/showthread...hreadid=553341 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a macro/formula
Select the column with the values having the minus sign following the number,
and run this code........use a copy of your data till you're sure all is well. Sub ReverseMinus() Dim rng As Range, cell As Range Set rng = Selection For Each cell In rng If Right(cell, 1) = "-" Then cell.Value = "-" & Mid(cell, Len(cell) - 1, 99) End If If Right(cell, 1) = "-" Then cell.Value = Mid(cell, 1, Len(cell) - 1) End If Next End Sub Vaya con Dios, Chuck, CABGx3 "brefed15" wrote: Perhaps this is a silly question, but i just can't figure out how to format this worksheet. The problem is that a lot of the cells have numbers in them with negative signs in the back instead of in the front (ie. 23-) and i was wondering if anyone knew of a macro/formula i could use to quickly fix this problem for all the cells. I would be extremely grateful. Thanks!!! -- brefed15 ------------------------------------------------------------------------ brefed15's Profile: http://www.excelforum.com/member.php...o&userid=35573 View this thread: http://www.excelforum.com/showthread...hreadid=553341 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a macro/formula
Hi!
One way: Select the cells in question Goto DataText to Columns Just click straight through: NextNextFinish That'll convert the TEXT entries like "23-" to negative numeric values -23. Biff "brefed15" wrote in message ... Perhaps this is a silly question, but i just can't figure out how to format this worksheet. The problem is that a lot of the cells have numbers in them with negative signs in the back instead of in the front (ie. 23-) and i was wondering if anyone knew of a macro/formula i could use to quickly fix this problem for all the cells. I would be extremely grateful. Thanks!!! -- brefed15 ------------------------------------------------------------------------ brefed15's Profile: http://www.excelforum.com/member.php...o&userid=35573 View this thread: http://www.excelforum.com/showthread...hreadid=553341 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a macro/formula
I think your sub needs tweaked!
50- converts to 0 1- converts properly to -1 10- converts to 0 55- converts to -5 Biff "CLR" wrote in message ... Select the column with the values having the minus sign following the number, and run this code........use a copy of your data till you're sure all is well. Sub ReverseMinus() Dim rng As Range, cell As Range Set rng = Selection For Each cell In rng If Right(cell, 1) = "-" Then cell.Value = "-" & Mid(cell, Len(cell) - 1, 99) End If If Right(cell, 1) = "-" Then cell.Value = Mid(cell, 1, Len(cell) - 1) End If Next End Sub Vaya con Dios, Chuck, CABGx3 "brefed15" wrote: Perhaps this is a silly question, but i just can't figure out how to format this worksheet. The problem is that a lot of the cells have numbers in them with negative signs in the back instead of in the front (ie. 23-) and i was wondering if anyone knew of a macro/formula i could use to quickly fix this problem for all the cells. I would be extremely grateful. Thanks!!! -- brefed15 ------------------------------------------------------------------------ brefed15's Profile: http://www.excelforum.com/member.php...o&userid=35573 View this thread: http://www.excelforum.com/showthread...hreadid=553341 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a macro/formula
Right you are Sir.........that's what I get for not testing.........my
apologies to the OP.........this should be better.... Sub ReverseMinus() Dim rng As Range, cell As Range Set rng = Selection For Each cell In rng If Right(cell, 1) = "-" Then cell.Value = "-" & Left(cell, Len(cell) - 1) End If Next End Sub Thanks for pointing that out Biff, Vaya con Dios, Chuck, CABGx3 "Biff" wrote: I think your sub needs tweaked! 50- converts to 0 1- converts properly to -1 10- converts to 0 55- converts to -5 Biff "CLR" wrote in message ... Select the column with the values having the minus sign following the number, and run this code........use a copy of your data till you're sure all is well. Sub ReverseMinus() Dim rng As Range, cell As Range Set rng = Selection For Each cell In rng If Right(cell, 1) = "-" Then cell.Value = "-" & Mid(cell, Len(cell) - 1, 99) End If If Right(cell, 1) = "-" Then cell.Value = Mid(cell, 1, Len(cell) - 1) End If Next End Sub Vaya con Dios, Chuck, CABGx3 "brefed15" wrote: Perhaps this is a silly question, but i just can't figure out how to format this worksheet. The problem is that a lot of the cells have numbers in them with negative signs in the back instead of in the front (ie. 23-) and i was wondering if anyone knew of a macro/formula i could use to quickly fix this problem for all the cells. I would be extremely grateful. Thanks!!! -- brefed15 ------------------------------------------------------------------------ brefed15's Profile: http://www.excelforum.com/member.php...o&userid=35573 View this thread: http://www.excelforum.com/showthread...hreadid=553341 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with a macro/formula
Thanks a lot Biff and Chuck!!! -- brefed15 ------------------------------------------------------------------------ brefed15's Profile: http://www.excelforum.com/member.php...o&userid=35573 View this thread: http://www.excelforum.com/showthread...hreadid=553341 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
search macro/formula help | Excel Discussion (Misc queries) | |||
search macro/formula help | Excel Discussion (Misc queries) | |||
search macro/formula help | Excel Discussion (Misc queries) | |||
Macro/Formula Help? | Excel Discussion (Misc queries) |