|
UBound
Im looping through a bunch of columns.
I want to store the column indexes 1, 9, 18, 21 in a cell, whose named range is Columns. Not sure how to€¦ WORKING rO = myRange.Rows.Count coLArray = Array(1, 9, 18, 21) For X_col = 0 To UBound(coLArray) For Loop_rO = 4 To rO Cells(Loop_rO, coLArray(X_col)).Select Y_CellBase = ActiveCell.Value Y_CellTest = Worksheets(2).Cells(Loop_rO, coLArray(X_col)) If Y_CellBase < Y_CellTest Then Worksheets(2).Cells(Loop_rO, coLArray(X_col)).Interior.ColorIndex = 3 End If Next Loop_rO Next X_col NOT WORKING rO = myRange.Rows.Count Columns2Check = Range("Columns") coLArray = Array(Columns2Check) For X_col = 0 To UBound(coLArray) For Loop_rO = 4 To rO Cells(Loop_rO, coLArray(X_col)).Select Y_CellBase = ActiveCell.Value Y_CellTest = Worksheets(2).Cells(Loop_rO, coLArray(X_col)) If Y_CellBase < Y_CellTest Then Worksheets(2).Cells(Loop_rO, coLArray(X_col)).Interior.ColorIndex = 3 End If Next Loop_rO Next X_col Appreciatively, Arturo |
UBound
Columns2Check = Range("Columns")
coLArray = Evaluate("{" & Columns2Check & "}") -- Regards, Tom Ogilvy "Arturo" wrote: Im looping through a bunch of columns. I want to store the column indexes 1, 9, 18, 21 in a cell, whose named range is Columns. Not sure how to€¦ WORKING rO = myRange.Rows.Count coLArray = Array(1, 9, 18, 21) For X_col = 0 To UBound(coLArray) For Loop_rO = 4 To rO Cells(Loop_rO, coLArray(X_col)).Select Y_CellBase = ActiveCell.Value Y_CellTest = Worksheets(2).Cells(Loop_rO, coLArray(X_col)) If Y_CellBase < Y_CellTest Then Worksheets(2).Cells(Loop_rO, coLArray(X_col)).Interior.ColorIndex = 3 End If Next Loop_rO Next X_col NOT WORKING rO = myRange.Rows.Count Columns2Check = Range("Columns") coLArray = Array(Columns2Check) For X_col = 0 To UBound(coLArray) For Loop_rO = 4 To rO Cells(Loop_rO, coLArray(X_col)).Select Y_CellBase = ActiveCell.Value Y_CellTest = Worksheets(2).Cells(Loop_rO, coLArray(X_col)) If Y_CellBase < Y_CellTest Then Worksheets(2).Cells(Loop_rO, coLArray(X_col)).Interior.ColorIndex = 3 End If Next Loop_rO Next X_col Appreciatively, Arturo |
UBound
also, if you use my approach, then your loop should be
For X_col = lbound(colArray) To UBound(coLArray) -- Regards, Tom Ogilvy "Arturo" wrote: Im looping through a bunch of columns. I want to store the column indexes 1, 9, 18, 21 in a cell, whose named range is Columns. Not sure how to€¦ WORKING rO = myRange.Rows.Count coLArray = Array(1, 9, 18, 21) For X_col = 0 To UBound(coLArray) For Loop_rO = 4 To rO Cells(Loop_rO, coLArray(X_col)).Select Y_CellBase = ActiveCell.Value Y_CellTest = Worksheets(2).Cells(Loop_rO, coLArray(X_col)) If Y_CellBase < Y_CellTest Then Worksheets(2).Cells(Loop_rO, coLArray(X_col)).Interior.ColorIndex = 3 End If Next Loop_rO Next X_col NOT WORKING rO = myRange.Rows.Count Columns2Check = Range("Columns") coLArray = Array(Columns2Check) For X_col = 0 To UBound(coLArray) For Loop_rO = 4 To rO Cells(Loop_rO, coLArray(X_col)).Select Y_CellBase = ActiveCell.Value Y_CellTest = Worksheets(2).Cells(Loop_rO, coLArray(X_col)) If Y_CellBase < Y_CellTest Then Worksheets(2).Cells(Loop_rO, coLArray(X_col)).Interior.ColorIndex = 3 End If Next Loop_rO Next X_col Appreciatively, Arturo |
UBound
Not sure how to tweak the working example to accept the columns to loop
through from the same info in a cell. "Arturo" wrote: Im looping through a bunch of columns. I want to store the column indexes 1, 9, 18, 21 in a cell, whose named range is Columns. Not sure how to€¦ WORKING rO = myRange.Rows.Count coLArray = Array(1, 9, 18, 21) For X_col = 0 To UBound(coLArray) For Loop_rO = 4 To rO Cells(Loop_rO, coLArray(X_col)).Select Y_CellBase = ActiveCell.Value Y_CellTest = Worksheets(2).Cells(Loop_rO, coLArray(X_col)) If Y_CellBase < Y_CellTest Then Worksheets(2).Cells(Loop_rO, coLArray(X_col)).Interior.ColorIndex = 3 End If Next Loop_rO Next X_col NOT WORKING rO = myRange.Rows.Count Columns2Check = Range("Columns") coLArray = Array(Columns2Check) For X_col = 0 To UBound(coLArray) For Loop_rO = 4 To rO Cells(Loop_rO, coLArray(X_col)).Select Y_CellBase = ActiveCell.Value Y_CellTest = Worksheets(2).Cells(Loop_rO, coLArray(X_col)) If Y_CellBase < Y_CellTest Then Worksheets(2).Cells(Loop_rO, coLArray(X_col)).Interior.ColorIndex = 3 End If Next Loop_rO Next X_col Appreciatively, Arturo |
UBound
Code hangs at,
Cells(Loop_rO, coLArray(X_col)).Select Subscript out of range. "Tom Ogilvy" wrote: Columns2Check = Range("Columns") coLArray = Evaluate("{" & Columns2Check & "}") -- Regards, Tom Ogilvy "Arturo" wrote: Im looping through a bunch of columns. I want to store the column indexes 1, 9, 18, 21 in a cell, whose named range is Columns. Not sure how to€¦ WORKING rO = myRange.Rows.Count coLArray = Array(1, 9, 18, 21) For X_col = 0 To UBound(coLArray) For Loop_rO = 4 To rO Cells(Loop_rO, coLArray(X_col)).Select Y_CellBase = ActiveCell.Value Y_CellTest = Worksheets(2).Cells(Loop_rO, coLArray(X_col)) If Y_CellBase < Y_CellTest Then Worksheets(2).Cells(Loop_rO, coLArray(X_col)).Interior.ColorIndex = 3 End If Next Loop_rO Next X_col NOT WORKING rO = myRange.Rows.Count Columns2Check = Range("Columns") coLArray = Array(Columns2Check) For X_col = 0 To UBound(coLArray) For Loop_rO = 4 To rO Cells(Loop_rO, coLArray(X_col)).Select Y_CellBase = ActiveCell.Value Y_CellTest = Worksheets(2).Cells(Loop_rO, coLArray(X_col)) If Y_CellBase < Y_CellTest Then Worksheets(2).Cells(Loop_rO, coLArray(X_col)).Interior.ColorIndex = 3 End If Next Loop_rO Next X_col Appreciatively, Arturo |
UBound
THANK You Tom!
"Tom Ogilvy" wrote: also, if you use my approach, then your loop should be For X_col = lbound(colArray) To UBound(coLArray) -- Regards, Tom Ogilvy "Arturo" wrote: Im looping through a bunch of columns. I want to store the column indexes 1, 9, 18, 21 in a cell, whose named range is Columns. Not sure how to€¦ WORKING rO = myRange.Rows.Count coLArray = Array(1, 9, 18, 21) For X_col = 0 To UBound(coLArray) For Loop_rO = 4 To rO Cells(Loop_rO, coLArray(X_col)).Select Y_CellBase = ActiveCell.Value Y_CellTest = Worksheets(2).Cells(Loop_rO, coLArray(X_col)) If Y_CellBase < Y_CellTest Then Worksheets(2).Cells(Loop_rO, coLArray(X_col)).Interior.ColorIndex = 3 End If Next Loop_rO Next X_col NOT WORKING rO = myRange.Rows.Count Columns2Check = Range("Columns") coLArray = Array(Columns2Check) For X_col = 0 To UBound(coLArray) For Loop_rO = 4 To rO Cells(Loop_rO, coLArray(X_col)).Select Y_CellBase = ActiveCell.Value Y_CellTest = Worksheets(2).Cells(Loop_rO, coLArray(X_col)) If Y_CellBase < Y_CellTest Then Worksheets(2).Cells(Loop_rO, coLArray(X_col)).Interior.ColorIndex = 3 End If Next Loop_rO Next X_col Appreciatively, Arturo |
| All times are GMT +1. The time now is 11:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com