Hey folks,
I am new to Excel programming and using oorexx to read a column of
cells in a worksheet checking for certain values and setting the
background color when the value matches. The code I have written works
fine, but being that it calls from rexx to an xlobj for each cell it
is not perhaps the most efficient way of doing it.
I was wondering if some kind soul could please advise if I am missing
a much more efficient method.
First I read from my database a series of text values into an array
with which to check each cell against. If the value matches one of the
46 values in the array then set the color, else next row, column.
-- ctl.1.i is the array values containing 46 string values
xlobj~ActiveSheet~UsedRange~select
iColumns = xlobj~Selection~Columns~Count
iRows = xlobj~Selection~Rows~Count
Do iC = 1 To iColumns
-- here I check the column name so that I do not have to do this for
EVERY column in the sheet.
-- check meets approx 10 cols of 40
If Lastpos('_COND',xlobj~Selection~Item(1, ic)~Value,) > 0 Then
Do
Do iR = 1 To iRows -- could be well over 100k rows
body = xlobj~Selection~Item(iR, iC)~Value
do i = 1 to ctl.0
If body = ctl.1.i then xlobj~Selection~Item(iR,
iC)~Interior~ColorIndex = 6
End -- Do ctl.0
End -- Do iRows
The rest of the code is of course a lot bigger but the above is the
area I was hoping to optimize.
Anyone with any bright ideas ?
Cheers, Fin.
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
