In a bank export spreadsheet, transaction amounts are listed in a
single column and are separated into credits and debits by color and
parenthesis (credit=black, debit=(red)).

I would like to run a sub to move all the debits into the next column,
and I'm guessing the best way to do this is by using and IF statement
that depends on the presence of red text or the parenthesis.

Any help on the routine will be appreciated.

The following looks at column A. If it finds cells with red font, it moves
the contents to column B:

Sub berrier()
Dim r As Range
Set r = Nothing
n = 100
For i = 1 To n
If Cells(i, "A").Font.ColorIndex = 3 Then
If r Is Nothing Then
Set r = Cells(i, "A")
Else
Set r = Union(r, Cells(i, "A"))
End If
End If
Next

r.Offset(0, 1) = r.Value
r.Clear
End Sub

I did not check the () as they may be only in the formatting.
--
Gary's Student
gsnu200703


"michaelberrier" wrote:

In a bank export spreadsheet, transaction amounts are listed in a
single column and are separated into credits and debits by color and
parenthesis (credit=black, debit=(red)).

I would like to run a sub to move all the debits into the next column,
and I'm guessing the best way to do this is by using and IF statement
that depends on the presence of red text or the parenthesis.

Any help on the routine will be appreciated.

On Feb 2, 7:11 pm, Gary''s Student
wrote:
The following looks at column A. If it finds cells with red font, it moves
the contents to column B:

Sub berrier()
Dim r As Range
Set r = Nothing
n = 100
For i = 1 To n
If Cells(i, "A").Font.ColorIndex = 3 Then
If r Is Nothing Then
Set r = Cells(i, "A")
Else
Set r = Union(r, Cells(i, "A"))
End If
End If
Next

r.Offset(0, 1) = r.Value
r.Clear
End Sub

I did not check the () as they may be only in the formatting.
--
Gary's Student
gsnu200703

"michaelberrier" wrote:
In a bank export spreadsheet, transaction amounts are listed in a
single column and are separated into credits and debits by color and
parenthesis (credit=black, debit=(red)).

I would like to run a sub to move all the debits into the next column,
and I'm guessing the best way to do this is by using and IF statement
that depends on the presence of red text or the parenthesis.

Any help on the routine will be appreciated.

Thanks for looking...

Tried that code adjusted for my column, which is D, and get this
error:

"Object variable or With block variable not set" on this line

r.Offset(0, 1) = r.Value

I've tried a little tweaking, but can't make it work.

Thanks again

Sub berrier()
Dim r As Range
Set r = Nothing
n = 100
For i = 1 To n
If Cells(i, "D").Font.ColorIndex = 3 Then
If r Is Nothing Then
Set r = Cells(i, "D")
Else
Set r = Union(r, Cells(i, "D"))
End If
End If
Next

r.Offset(0, 1) = r.Value
r.Clear
End Sub


The error means that the code did not find any cells in column D with Red
font (#3). For this type of code to work, the color must be "firm", that is
not the result of conditional formatting.
--
Gary's Student
gsnu200703


"michaelberrier" wrote:

On Feb 2, 7:11 pm, Gary''s Student
wrote:
The following looks at column A. If it finds cells with red font, it moves
the contents to column B:

Sub berrier()
Dim r As Range
Set r = Nothing
n = 100
For i = 1 To n
If Cells(i, "A").Font.ColorIndex = 3 Then
If r Is Nothing Then
Set r = Cells(i, "A")
Else
Set r = Union(r, Cells(i, "A"))
End If
End If
Next

r.Offset(0, 1) = r.Value
r.Clear
End Sub

I did not check the () as they may be only in the formatting.
--
Gary's Student
gsnu200703

"michaelberrier" wrote:
In a bank export spreadsheet, transaction amounts are listed in a
single column and are separated into credits and debits by color and
parenthesis (credit=black, debit=(red)).

I would like to run a sub to move all the debits into the next column,
and I'm guessing the best way to do this is by using and IF statement
that depends on the presence of red text or the parenthesis.

Any help on the routine will be appreciated.

Thanks for looking...

Tried that code adjusted for my column, which is D, and get this
error:

"Object variable or With block variable not set" on this line

r.Offset(0, 1) = r.Value

I've tried a little tweaking, but can't make it work.

Thanks again

On Feb 2, 8:20 pm, Gary''s Student
wrote:
Sub berrier()
Dim r As Range
Set r = Nothing
n = 100
For i = 1 To n
If Cells(i, "D").Font.ColorIndex = 3 Then
If r Is Nothing Then
Set r = Cells(i, "D")
Else
Set r = Union(r, Cells(i, "D"))
End If
End If
Next

r.Offset(0, 1) = r.Value
r.Clear
End Sub

The error means that the code did not find any cells in column D with Red
font (#3). For this type of code to work, the color must be "firm", that is
not the result of conditional formatting.
--
Gary's Student
gsnu200703

"michaelberrier" wrote:
On Feb 2, 7:11 pm, Gary''s Student
wrote:
The following looks at column A. If it finds cells with red font, it moves
the contents to column B:

Sub berrier()
Dim r As Range
Set r = Nothing
n = 100
For i = 1 To n
If Cells(i, "A").Font.ColorIndex = 3 Then
If r Is Nothing Then
Set r = Cells(i, "A")
Else
Set r = Union(r, Cells(i, "A"))
End If
End If
Next

r.Offset(0, 1) = r.Value
r.Clear
End Sub

I did not check the () as they may be only in the formatting.
--
Gary's Student
gsnu200703

"michaelberrier" wrote:
In a bank export spreadsheet, transaction amounts are listed in a
single column and are separated into credits and debits by color and
parenthesis (credit=black, debit=(red)).

I would like to run a sub to move all the debits into the next column,
and I'm guessing the best way to do this is by using and IF statement
that depends on the presence of red text or the parenthesis.

Any help on the routine will be appreciated.

Thanks for looking...

Tried that code adjusted for my column, which is D, and get this
error:

"Object variable or With block variable not set" on this line

r.Offset(0, 1) = r.Value

I've tried a little tweaking, but can't make it work.

Thanks again

Ok...I'm an idiot.

The number isn't Red...it's a negative number formatted as
(Red)...talk about a rookie mistake.

On Feb 2, 8:28 pm, "michaelberrier" wrote:
On Feb 2, 8:20 pm, Gary''s Student



wrote:
Sub berrier()
Dim r As Range
Set r = Nothing
n = 100
For i = 1 To n
If Cells(i, "D").Font.ColorIndex = 3 Then
If r Is Nothing Then
Set r = Cells(i, "D")
Else
Set r = Union(r, Cells(i, "D"))
End If
End If
Next

r.Offset(0, 1) = r.Value
r.Clear
End Sub

The error means that the code did not find any cells in column D with Red
font (#3). For this type of code to work, the color must be "firm", that is
not the result of conditional formatting.
--
Gary's Student
gsnu200703

"michaelberrier" wrote:
On Feb 2, 7:11 pm, Gary''s Student
wrote:
The following looks at column A. If it finds cells with red font, it moves
the contents to column B:

Sub berrier()
Dim r As Range
Set r = Nothing
n = 100
For i = 1 To n
If Cells(i, "A").Font.ColorIndex = 3 Then
If r Is Nothing Then
Set r = Cells(i, "A")
Else
Set r = Union(r, Cells(i, "A"))
End If
End If
Next

r.Offset(0, 1) = r.Value
r.Clear
End Sub

I did not check the () as they may be only in the formatting.
--
Gary's Student
gsnu200703

"michaelberrier" wrote:
In a bank export spreadsheet, transaction amounts are listed in a
single column and are separated into credits and debits by color and
parenthesis (credit=black, debit=(red)).

I would like to run a sub to move all the debits into the next column,
and I'm guessing the best way to do this is by using and IF statement
that depends on the presence of red text or the parenthesis.

Any help on the routine will be appreciated.

Thanks for looking...

Tried that code adjusted for my column, which is D, and get this
error:

"Object variable or With block variable not set" on this line

r.Offset(0, 1) = r.Value

I've tried a little tweaking, but can't make it work.

Thanks again

Ok...I'm an idiot.

The number isn't Red...it's a negative number formatted as
(Red)...talk about a rookie mistake.

Alright, I changed this:
If Cells(i, "D").Font.ColorIndex = 3 Then
To this:
If Cells(i, "D") < 0

Now, it moves all the negative numbers to the next column, but then
there is weirdness:
Some cells transfer correctly;
Some transfer as $200, regardless of value
Others end up as #N/A errors...

What have I done?