Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > UBound
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 108
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 108
Default 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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 108
Default 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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 108
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Why is this returning a Ubound value of zero [email protected] Excel Discussion (Misc queries) 1 September 28th 07 07:44 PM
resize(Ubound, Lbound) ina Excel Programming 4 May 1st 06 02:55 PM
Array Ubound gti_jobert[_71_] Excel Programming 6 March 27th 06 10:34 PM
Ubound & Lbound Michael168[_80_] Excel Programming 3 June 1st 04 02:00 PM


All times are GMT +1. The time now is 03:31 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement