Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to tell if cell value is one of standard set
I have a long list of items in a column with the field heading
"products" in an excel worksheet. Some of these items are "standard" whiile some are not. The full reference set of standard items are in a separate worksheet called "Standards" where there is a column with the field heading "products". I'd like for excel to go to the first cell under the heading "products" where I have the list of items and to check whether this item is one of the standards by looking in the reference worksheet "Standards" under the heading "products". If it is one of the "standards", then go to procedure 1. If it is not one of the standards", then list this in a separate newly created worksheet. So that there is a list of all non-standard items in the new worksheet. Thanks. Matthew Kramer *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to tell if cell value is one of standard set
Assume column A is the product column on the list to be checked found on
Sheet1 Assume column F is the product column on the list to check against found on sheet Standards Assume nonstandard data is copied to sheet NonStandard With worksheets("Sheet1") set rng = .Range(.Cells(2,1),.Cells(rows.count,1).end(xlup)) End With With Worksheets("Standards") set rng2 = .Range(.Cells(2,"F"),.Cells(rows.count,"F").End(xl up)) End with for each cell in rng if application.Countif(rng2,cell) 0 then ' found in standard Procedure1 else ' not found in standardd cell.Entirerow.copy Destination:=worksheets("NonStandard") _ .Cells(rows.count,1).End(xlup)(2) end if Next -- Regards, Tom Ogilvy "Matthew Kramer" wrote in message ... I have a long list of items in a column with the field heading "products" in an excel worksheet. Some of these items are "standard" whiile some are not. The full reference set of standard items are in a separate worksheet called "Standards" where there is a column with the field heading "products". I'd like for excel to go to the first cell under the heading "products" where I have the list of items and to check whether this item is one of the standards by looking in the reference worksheet "Standards" under the heading "products". If it is one of the "standards", then go to procedure 1. If it is not one of the standards", then list this in a separate newly created worksheet. So that there is a list of all non-standard items in the new worksheet. Thanks. Matthew Kramer *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to tell if cell value is one of standard set
I don't think there is a visual indicator: however, the general convention is
for numbers to be right justified and text to be left justified by default. Programatically: typename(Range("A1").Value) This returns the type for cell A1. "Matthew Kramer" wrote: I have a long list of items in a column with the field heading "products" in an excel worksheet. Some of these items are "standard" whiile some are not. The full reference set of standard items are in a separate worksheet called "Standards" where there is a column with the field heading "products". I'd like for excel to go to the first cell under the heading "products" where I have the list of items and to check whether this item is one of the standards by looking in the reference worksheet "Standards" under the heading "products". If it is one of the "standards", then go to procedure 1. If it is not one of the standards", then list this in a separate newly created worksheet. So that there is a list of all non-standard items in the new worksheet. Thanks. Matthew Kramer *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Standard Deviation | Excel Discussion (Misc queries) | |||
standard deviation | Excel Discussion (Misc queries) | |||
Standard Deviation | Excel Worksheet Functions | |||
Standard Error | Excel Worksheet Functions | |||
Insert a standard character in a cell of excel. | Excel Worksheet Functions |