My financial report with several columns and all the rows displays numeric
values as text with DB and CR that I need to convert to positive and negative
numerics. The following function does the job, but is there a macro
available that would do it?

=IF(RIGHT(E12,3)=" cr",1*LEFT(E12,LEN(E12)-3),-1*LEFT(E12,LEN(E12)-3))

Select the cells you want to convert and run:

Sub FixThem()
For Each r In Selection
v = r.Value
vn = Left(v, Len(v) - 3)
If Right(v, 2) = "DB" Then
r.Value = -vn
Else
r.Value = vn
End If
Next
End Sub
--
Gary''s Student - gsnu200909


"bud i" wrote:

> My financial report with several columns and all the rows displays numeric
> values as text with DB and CR that I need to convert to positive and negative
> numerics. The following function does the job, but is there a macro
> available that would do it?
>
> =IF(RIGHT(E12,3)=" cr",1*LEFT(E12,LEN(E12)-3),-1*LEFT(E12,LEN(E12)-3))

You didn't say which columns, so I just used A, C:E and H in my example,
just change the Range assignment in the Set statement to the columns that
have your credit/debit values in this macro...

Sub FixCreditDebitCells()
Dim R As Range, C As Range
Set R = Range("A:A,C:E,H:H")
R.Replace " cr", "", MatchCase:=False
R.Replace " db", "-", MatchCase:=False
For Each C In R.Columns
C.TextToColumns C, TrailingMinusNumbers:=True
Next
End Sub

--
Rick (MVP - Excel)


"bud i" <bud (E-Mail Removed)> wrote in message
news:59B5CD78-AC95-4B30-9DE8-(E-Mail Removed)...
> My financial report with several columns and all the rows displays numeric
> values as text with DB and CR that I need to convert to positive and
> negative
> numerics. The following function does the job, but is there a macro
> available that would do it?
>
> =IF(RIGHT(E12,3)=" cr",1*LEFT(E12,LEN(E12)-3),-1*LEFT(E12,LEN(E12)-3))

The code I posted *could* affect any headers the specified columns **if and
only if** they contain the letters <space>cr or <space>db in them (they
would get replaced). If you have such a situation, then use this code in
place of the code I posted originally, or simply use it instead of my
originally posted code (it will be as speedy as my originally posted code
was)...

Sub FixCreditDebitCells()
Dim R As Range, C As Range
Set R = Intersect(Range("A:A,C:E,H:H"), ActiveSheet.UsedRange).Offset(1)
R.Replace " cr", "", MatchCase:=False
R.Replace " db", "-", MatchCase:=False
For Each C In R.Columns
C.TextToColumns C, TrailingMinusNumbers:=True
Next
End Sub

If you have two (or more) header rows, then change the value in the Offset
property call to that number of rows.

--
Rick (MVP - Excel)


"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> You didn't say which columns, so I just used A, C:E and H in my example,
> just change the Range assignment in the Set statement to the columns that
> have your credit/debit values in this macro...
>
> Sub FixCreditDebitCells()
> Dim R As Range, C As Range
> Set R = Range("A:A,C:E,H:H")
> R.Replace " cr", "", MatchCase:=False
> R.Replace " db", "-", MatchCase:=False
> For Each C In R.Columns
> C.TextToColumns C, TrailingMinusNumbers:=True
> Next
> End Sub
>
> --
> Rick (MVP - Excel)
>
>
> "bud i" <bud (E-Mail Removed)> wrote in message
> news:59B5CD78-AC95-4B30-9DE8-(E-Mail Removed)...
>> My financial report with several columns and all the rows displays
>> numeric
>> values as text with DB and CR that I need to convert to positive and
>> negative
>> numerics. The following function does the job, but is there a macro
>> available that would do it?
>>
>> =IF(RIGHT(E12,3)=" cr",1*LEFT(E12,LEN(E12)-3),-1*LEFT(E12,LEN(E12)-3))
>