# Macro to find cells and flip signs

#### erock24

##### Well-known Member
I have a report that kicks into excel that makes credit numbers show up as say "45.36CR" the debits come in as "45.36". I was hoping for a macro that could locate all cells in columns D:E with "CR" and delete the "CR" then flip the sign of the number to neg. So the outcome would be "(45.36)" Leave all other cells (debits) alone. Also, the amounts of the numbers will be varied, some in the 1000's, some 100's, ect.

Thank you very much for your time and help

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
How's this:
Code:
``````Sub FlipMe()
Dim a As Long
Dim Limit As Long
Limit = Cells(Rows.Count, 4).End(xlUp).Row
a = Cells(Rows.Count, 5).End(xlUp).Row
If Limit < a Then Limit = a
For a = 1 To Limit
If Right(Cells(a, 4), 2) = "CR" Then
Cells(a, 4) = Val(Left(Cells(a, 4), Len(Cells(a, 4)) - 2)) * -1
End If
If Right(Cells(a, 5), 2) = "CR" Then
Cells(a, 5) = Val(Left(Cells(a, 5), Len(Cells(a, 5)) - 2)) * -1
End If
Next a
End Sub``````

Code:
``````Sub FlipSign()
For Each cl In Range("D:E")
If Right(cl.Value, 2) = "CR" Then
cl.Value = CDbl(Left(cl.Value, Len(cl.Value) - 2)) * (-1)
End If
Next cl
End Sub``````

Wow!! Both macros work perfectly.

Thank you very much.

Replies
11
Views
597
Replies
4
Views
249
Replies
0
Views
277
Replies
0
Views
316
Replies
20
Views
700

1,221,406
Messages
6,159,694
Members
451,586
Latest member
khaledshahin

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back