On Feb 3, 1:36*pm, "Clif McIrvin" wrote:
010633143-5 2004,010633143-5 2005,010633143-5 2006,010633143-5
2007,010633143-5 2008,010633143-5 2009,,,,

How can I clear a cell in cols R thru AA if it matches the "control-
number" (in col A) AND the "year" in cols B thru I

I don't know of a way to clear a cell without using a macro or the user
interface...

Are you a VBA programmer at all? You can use the split() function in VBA
to create an array: [ TestValue = split(range("R1")," ") ] would give
you:
* * TestValue(0) as the control number in R1, and
* * TestValue(1) as the year in R1

From there you can test against A1 for the control number, and use the
find method against B1:I1.

You can use the macro recorder to see how to use the find method.

Maybe this will get you started.

Clif

"gcotterl" wrote in message

...





Each cell in col A contains a "control-number".
Each cell in cols B thru I contains a "year".
For example:

010595369-6,2008,2009,,,,,,
010595370-6,2008,2009,,,,,,
010597700-7,2009,,,,,,,
010597703-0,2007,2008,2009,,,,,
010598830-4,2008,2009,,,,,,
010599037-8,2007,2008,,,,,,
010631923-7,2009,,,,,,,
010632739-1,2005,2006,2007,2008,,,,
010632814-5,2009,,,,,,,
010633029-6,2009,,,,,,,
010633143-5,2009,,,,,,,

Each cell in cols R thru AA contains a "control-number" AND a year.
For example:

010595369-6 2003,010595369-6 2004,010595369-6 2005,010595369-6
2006,010595369-6 2007,010595369-6 2008,010595369-6 2009,,,

010595370-6 2003,010595370-6 2004,010595370-6 2005,010595370-6
2006,010595370-6 2007,010595370-6 2008,010595370-6 2009,,,

010597700-7 2004,010597700-7 2005,010597700-7 2006,010597700-7
2007,010597700-7 2008,010597700-7 2009,,,,

010597703-0 2004,010597703-0 2005,010597703-0 2006,010597703-0
2007,010597703-0 2008,010597703-0 2009,,,,

010597969-4 2004,010597969-4 2005,010597969-4 2006,010597969-4
2007,010597969-4 2008,010597969-4 2009,,,,

010598830-4 2003,010598830-4 2004,010598830-4 2005,010598830-4
2006,010598830-4 2007,010598830-4 2008,010598830-4 2009,,,

010599037-8 2003,010599037-8 2004,010599037-8 2005,010599037-8
2006,010599037-8 2007,010599037-8 2008,010599037-8 2009,,,

010630761-4 2007,010630761-4 2008,010630761-4 2009,,,,,,,

010631923-7 2004,010631923-7 2005,010631923-7 2006,010631923-7
2007,010631923-7 2008,010631923-7 2009,,,,

010632313-9 2009,,,,,,,,,

010632314-0 2009,,,,,,,,,

010632739-1 2000,010632739-1 2001,010632739-1 2002,010632739-1
2003,010632739-1 2004,010632739-1 2005,010632739-1 2006,010632739-1
2007,010632739-1 2008,010632739-1 2009

010632814-5 2004,010632814-5 2005,010632814-5 2006,010632814-5
2007,010632814-5 2008,010632814-5 2009,,,,

010632843-1 2009,,,,,,,,,

010633029-6 2005,010633029-6 2006,010633029-6 2007,010633029-6
2008,010633029-6 2009,,,,,

010633143-5 2004,010633143-5 2005,010633143-5 2006,010633143-5
2007,010633143-5 2008,010633143-5 2009,,,,

How can I clear a cell in cols R thru AA if it matches the "control-
number" (in col A) AND the "year" in cols B thru I

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)- Hide quoted text -

- Show quoted text -

No, I'm not familiar with VBA.

I guess I'll have to visually compare the control-number (in col A)
and the year (in cols B thru I) with the contents of the cells in cols
R thru AA and delete the matches. (Col A has 2,900 rows and col RR
has 5,000 rows)