Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically changing cell information
Hey all, Ive tried to figure out this problem myself, but I just dont know where to look. Heres my problem: Everyday I generate a report that shows debits and credits. Any credits are shown as a '50.00CR', where as debits are shown as '50.00'. I am wondering if it is possible to change the formatting so any cell shown as '50.00CR' is changed to '-50.00' something of that nature. As of now its a few thousand entries everyday and can take up to 20-30 minutes of going through the lines and manually changing them. Any solution is viable, I just need something so when I select the column with all of these values and run a sum(), excel picks up that the 50.00CR are indeed, negative values in the equation. Thanks for any help with this problem. -- BBurz ------------------------------------------------------------------------ BBurz's Profile: http://www.excelforum.com/member.php...o&userid=36269 View this thread: http://www.excelforum.com/showthread...hreadid=560551 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically changing cell information
Hi!
Any solution is viable Ok, the next time you need to do this turn on your macro recorder. Assume the values are in column A. Select column A Goto the menu EditReplace Find what: CR Replace with: - Replace All Click OK on the replacement pop up Click Close Goto the menu DataText to Columns Select Delimited Click Finish Now, just run this macro when you need to do this in the future. Biff "BBurz" wrote in message ... Hey all, Ive tried to figure out this problem myself, but I just dont know where to look. Heres my problem: Everyday I generate a report that shows debits and credits. Any credits are shown as a '50.00CR', where as debits are shown as '50.00'. I am wondering if it is possible to change the formatting so any cell shown as '50.00CR' is changed to '-50.00' something of that nature. As of now its a few thousand entries everyday and can take up to 20-30 minutes of going through the lines and manually changing them. Any solution is viable, I just need something so when I select the column with all of these values and run a sum(), excel picks up that the 50.00CR are indeed, negative values in the equation. Thanks for any help with this problem. -- BBurz ------------------------------------------------------------------------ BBurz's Profile: http://www.excelforum.com/member.php...o&userid=36269 View this thread: http://www.excelforum.com/showthread...hreadid=560551 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically changing cell information
hi, i am new VBA learner , i write a VBA for your case.
you can insert it as the Command button code. Please try. and i welcome experts opinions on it. if you have data in column A and you want the result in column B, then, Private Sub CommandButton1_Click() lastrow = Range("a65536").End(xlUp).Row For i = 1 To lastrow tempv = Cells(i, 1).Value If Right(tempv, 2) = "CR" Then Cells(i, 2).Value = "-" & Left(tempv, Len(tempv) - 2) Else: Cells(i, 2).Value = Cells(i, 1).Value End If Next i End Sub €œBBurz€ç¼–写: Hey all, Ive tried to figure out this problem myself, but I just dont know where to look. Heres my problem: Everyday I generate a report that shows debits and credits. Any credits are shown as a '50.00CR', where as debits are shown as '50.00'. I am wondering if it is possible to change the formatting so any cell shown as '50.00CR' is changed to '-50.00' something of that nature. As of now its a few thousand entries everyday and can take up to 20-30 minutes of going through the lines and manually changing them. Any solution is viable, I just need something so when I select the column with all of these values and run a sum(), excel picks up that the 50.00CR are indeed, negative values in the equation. Thanks for any help with this problem. -- BBurz ------------------------------------------------------------------------ BBurz's Profile: http://www.excelforum.com/member.php...o&userid=36269 View this thread: http://www.excelforum.com/showthread...hreadid=560551 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically changing cell information
I owe you guys 20 minutes of everyday that I work. Thank you so much! -- BBurz ------------------------------------------------------------------------ BBurz's Profile: http://www.excelforum.com/member.php...o&userid=36269 View this thread: http://www.excelforum.com/showthread...hreadid=560551 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
insert date | Excel Worksheet Functions | |||
format cell to know number of another cell automatically | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
CELLS HAVING SAME NUMBER BY CHANGING ANY CELL | Excel Discussion (Misc queries) |