Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Cell Contents
Here is my code so far.
Private Sub CommandButton1_Click() Dim rng As Range, rng1 As Range Dim cell As Range Set rng = Range(Cells(1, "O"), Cells(Rows.Count, "O").End(xlUp)) For Each cell In rng If cell.Font.ColorIndex = 6 Or cell.Font.ColorIndex = 3 Then If rng1 Is Nothing Then Set rng1 = cell Else Set rng1 = Union(rng1, cell) End If End If Next cell If Not rng1 Is Nothing Then rng1.EntireRow.Copy Destination:= _ Worksheets("Sheet3").Cells(Rows.Count, 1).End(xlUp) Else MsgBox "No cells met criteria" End If This copys a row if the font in column O is red or yellow. I am also trying to see if it has text in columns A or B and if there is no contents, I want it to copy the cell above it. For example: A B ------------------------- O 5 1 1 6 1 7 8 red font The code would then copy cells A5 & B6 to A8 & B8 on the other sheet |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Cell Contents
Your example is too screwed up to be much help.
Are you looking for a completely different macro that looks only at columns A and B and only copies columns A and B (makes no difference about the color in O). Why would 5 and 6 go to row 8 on the other sheet? (I understand that if I look at row 6, then sinceA6 is blank use A5 and since B6 is not blank, use B6) Another question: if A6 were blank, would A5 always have a value or would one need to look at A4, then A3 to find a value? Do you really just want all filled cells from A & B with emty cells paired with filled cells filled from the value above? -- Regards, Tom Ogilvy "Jeff at Robert Bosch Corp." wrote in message ... Here is my code so far. Private Sub CommandButton1_Click() Dim rng As Range, rng1 As Range Dim cell As Range Set rng = Range(Cells(1, "O"), Cells(Rows.Count, "O").End(xlUp)) For Each cell In rng If cell.Font.ColorIndex = 6 Or cell.Font.ColorIndex = 3 Then If rng1 Is Nothing Then Set rng1 = cell Else Set rng1 = Union(rng1, cell) End If End If Next cell If Not rng1 Is Nothing Then rng1.EntireRow.Copy Destination:= _ Worksheets("Sheet3").Cells(Rows.Count, 1).End(xlUp) Else MsgBox "No cells met criteria" End If This copys a row if the font in column O is red or yellow. I am also trying to see if it has text in columns A or B and if there is no contents, I want it to copy the cell above it. For example: A ------------------------- O 5 1 1 6 1 7 8 red font The code would then copy cells A5 & B6 to A8 & B8 on the other sheet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Cell Contents
After the first macro copys all the rows that have r&y font, if there is no
value in A or B then I want it to copy the next value above. If this has to be done under another command button that is fine. I guess what I am asking doesn't depend on the first macro. It may be 1,2,3,4,5... rows above. Columns A & B have different text in them as you go down the columns. So, it has to work this way where it searches for the text above, and doesn't just fill all the cells in the new sheet with the cells from the old that have text. Please let me know if you still don't know what I am trying to accomplish. Thanks "Tom Ogilvy" wrote: Your example is too screwed up to be much help. Are you looking for a completely different macro that looks only at columns A and B and only copies columns A and B (makes no difference about the color in O). Why would 5 and 6 go to row 8 on the other sheet? (I understand that if I look at row 6, then sinceA6 is blank use A5 and since B6 is not blank, use B6) Another question: if A6 were blank, would A5 always have a value or would one need to look at A4, then A3 to find a value? Do you really just want all filled cells from A & B with emty cells paired with filled cells filled from the value above? -- Regards, Tom Ogilvy "Jeff at Robert Bosch Corp." wrote in message ... Here is my code so far. Private Sub CommandButton1_Click() Dim rng As Range, rng1 As Range Dim cell As Range Set rng = Range(Cells(1, "O"), Cells(Rows.Count, "O").End(xlUp)) For Each cell In rng If cell.Font.ColorIndex = 6 Or cell.Font.ColorIndex = 3 Then If rng1 Is Nothing Then Set rng1 = cell Else Set rng1 = Union(rng1, cell) End If End If Next cell If Not rng1 Is Nothing Then rng1.EntireRow.Copy Destination:= _ Worksheets("Sheet3").Cells(Rows.Count, 1).End(xlUp) Else MsgBox "No cells met criteria" End If This copys a row if the font in column O is red or yellow. I am also trying to see if it has text in columns A or B and if there is no contents, I want it to copy the cell above it. For example: A ------------------------- O 5 1 1 6 1 7 8 red font The code would then copy cells A5 & B6 to A8 & B8 on the other sheet |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding the Row number based on contents of a cell | Excel Worksheet Functions | |||
finding the cell with min value and returning the contents of neig | Excel Worksheet Functions | |||
Finding row # of last cell containing contents | Excel Worksheet Functions | |||
Finding cell contents based on certain criteria | Excel Discussion (Misc queries) | |||
Finding cell contents based on certain criteria | Excel Discussion (Misc queries) |