Hi Rob,
Yeah, sorry about that. Countif is pretty slow when there's lots of data. By
the way, a macro will be slower, but has the distinct advantage that you can
run it only when convenient.
Just looking at your code, I see that you are using the Cells thingy.
When using Cells, you cant refer to column by their letters, like you can when
you use Range only.
I think the following line is incorrect for 2 reasons:
Set rangeICD9 = Sheets("ICD9Codes").Range(Cells(2, "B"), Cells(7000,
"B").End(1))
You have used "B" for the column ref, but you need to use the actual column
number.
Also, I think the last bracket is in the wrong place. I think the line should
look like this:
Set rangeICD9 = Sheets("ICD9Codes").Range(Cells(2, 2), Cells(7000, 2)).End(1)
Notice also that the double quotes are also not used.
Go through your code, replacing all alphabet column refs with numbers in any
place where you have used the Cells thingy.
The line with "W" in it also has a missplaced bracket, same as above.
Regards - Dave.
> Date: Mon, 5 Jan 2009 17:24:35 -0800
> Subject: $$Excel-Macros$$ Re: Validating a column of numbers against another
> worksheet
> From: robbuonoc...@infocore.us
> To: excel-macros@googlegroups.com
>
>
> Dave:
>
> Thanks for your response. I tried this solution but ran into
> performance issues. There are 10 columns that hold values and
> sometimes hundreds of rows of data. There are also approx. 20,000
> valid ICD9 codes that this is validating against. Moving around the
> spreadsheet caused serious delays.
> > > I have a list of Diagnosis codes entered into 5 columns on a
> > > worksheet. The second worksheet, named ICD9 Codes, lists all the
> > > possible ICD9 Codes that are valid. I want to create a macro that you
> > > could activate at the top of a column and it would validate each
> > > number against the list in the second worksheet. If the value was
> > > valid, it would set the color of the cell to green, if it was not
> > > valid, it would set the color to red.
> >
> > > Here is the code I have so far.
> >
> > > Sub ValidateICD9()
> > > '
> > > ' ValidateICD9 Macro
> > > ' Keyboard Shortcut: Ctrl+z
> > > '
> > > Dim iRow As Long
> > > Dim rangeICD9 As Range
> > > Dim rCell As Range
> >
> > > ' Select Source Data Worksheet
> >
> > > Set rangeICD9 = Sheets("ICD9Codes").Range(Cells(2, "B"), Cells
> > > (7000, "B").End(1))
> > > Sheets("ICD9").Select
> >
> > > For Each rCell In Range(Cells(2, "W"), Cells(Rows.Count, "W").End
> > > (xlUp))
> > > If rangeICD9.Find(what:=rCell, LookIn:=xlValues,
> > > LookAt:=xlWhole) Is Nothing Then
> > > rCell.Font.ColorIndex = 3
> > > Else
> > > rCell.Font.ColorIndex = 3
> > > End If
> >
> > > Next
> > > End Sub
> >
> > > When I run this, I get a message that Subscript is out of range.
> > > Right now its set for only column W. I don't know how to set this to
> > > the active column.
> >
> > > Any help would be appreciated.
> >
> > > Thanks.
> >
> > > Rob Buonocore
> >
> > _________________________________________________________________
> > Holiday cheer from Messenger. Download free emoticons
> > today!http://livelife.ninemsn.com.au/article.aspx?id=669758- Hide quoted
> > text -
> >
> > - Show quoted text -
>
> >
_________________________________________________________________
Messenger's gift to you! Download free emoticons today!
http://livelife.ninemsn.com.au/article.aspx?id=669758
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
If you find any spam message in the group, please send an email to Ayush @
jainayus...@gmail.com
-~----------~----~----~----~------~----~------~--~---
