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

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

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

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