Automatically changing cell information

B

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.
 
B

Biff

Hi!
Any solution is viable
Click to expand...

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 Edit>Replace
Find what: CR
Replace with: -
Replace All
Click OK on the replacement pop up
Click Close
Goto the menu Data>Text to Columns
Select Delimited
Click Finish

Now, just run this macro when you need to do this in the future.

Biff
 
W

wdjsxj

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â€ç¼–写：
 
Top