how to tell if cell value is one of standard set

[Matthew Kramer]

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!

 

[Tom Ogilvy]

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(xlup))
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

 

[AA2e72E]

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.