Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cut/paste cells based on text color or certain characters
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cut/paste cells based on text color or certain characters
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cut/paste cells based on text color or certain characters
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cut/paste cells based on text color or certain characters
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cut/paste cells based on text color or certain characters
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cut/paste cells based on text color or certain characters
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cut/paste cells based on text color or certain characters
On Feb 2, 8:38 pm, "michaelberrier" wrote:
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? Update.... Only the first three negative numbered cells transfer correctly, the rest.... -$200 is the first negative value. It is copied on most of the following cells. This duplicates regardless of what value is in the first cell, so it seems something in the code is hung on the first value. The values from the second and third negative cell appear occasionally, and always sequentially. Other values return as #N/A.... The code works perfectly on the Red text cells as described before. It seems the problem occurs after a cell that doesn't fit the criteria (i.e.: a positive value). Something seems broken in the second part of the IF statement then, I would think. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cut/paste cells based on text color or certain characters
You have done a good job modifying the code on your own and you were very
close. Sub berrier_II() ' ' The Sequel ' Dim r As Range, rr As Range Set r = Nothing n = 100 For i = 1 To n If Cells(i, "D").Value < 0 Then If r Is Nothing Then Set r = Cells(i, "D") Else Set r = Union(r, Cells(i, "D")) End If End If Next For Each rr In r rr.Copy rr.Offset(0, 1) rr.Clear Next End Sub Here we modified the test in the loop. We also modified the last part to do a Copy/Paste. It should work unless there are any formula references that might get botched. Give it a try and let me know how you made out. In Any Case: Have a good weekend! -- Gary's Student gsnu200703 "michaelberrier" wrote: On Feb 2, 8:38 pm, "michaelberrier" wrote: 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? Update.... Only the first three negative numbered cells transfer correctly, the rest.... -$200 is the first negative value. It is copied on most of the following cells. This duplicates regardless of what value is in the first cell, so it seems something in the code is hung on the first value. The values from the second and third negative cell appear occasionally, and always sequentially. Other values return as #N/A.... The code works perfectly on the Red text cells as described before. It seems the problem occurs after a cell that doesn't fit the criteria (i.e.: a positive value). Something seems broken in the second part of the IF statement then, I would think. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cut/paste cells based on text color or certain characters
On Feb 3, 6:18 am, Gary''s Student
wrote: You have done a good job modifying the code on your own and you were very close. Sub berrier_II() ' ' The Sequel ' Dim r As Range, rr As Range Set r = Nothing n = 100 For i = 1 To n If Cells(i, "D").Value < 0 Then If r Is Nothing Then Set r = Cells(i, "D") Else Set r = Union(r, Cells(i, "D")) End If End If Next For Each rr In r rr.Copy rr.Offset(0, 1) rr.Clear Next End Sub Here we modified the test in the loop. We also modified the last part to do a Copy/Paste. It should work unless there are any formula references that might get botched. Give it a try and let me know how you made out. In Any Case: Have a good weekend! -- Gary's Student gsnu200703 "michaelberrier" wrote: On Feb 2, 8:38 pm, "michaelberrier" wrote: 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? Update.... Only the first three negative numbered cells transfer correctly, the rest.... -$200 is the first negative value. It is copied on most of the following cells. This duplicates regardless of what value is in the first cell, so it seems something in the code is hung on the first value. The values from the second and third negative cell appear occasionally, and always sequentially. Other values return as #N/A.... The code works perfectly on the Red text cells as described before. It seems the problem occurs after a cell that doesn't fit the criteria (i.e.: a positive value). Something seems broken in the second part of the IF statement then, I would think. That did it. I guess the second range, rr, was the ticket, eh? Thanks again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert a month to a quarter ...... | New Users to Excel | |||
Adding numbers based on partial text in adjacent cells | Excel Discussion (Misc queries) | |||
merged cells into one text cell, size varies dependant on text dat | Excel Discussion (Misc queries) | |||
Linked cells and text boxes | Excel Discussion (Misc queries) | |||
conditional formating - Highlighting text cells based on sales res | Excel Discussion (Misc queries) |