Evaluating criteria within certain columns

[mike.wilson8]

I'm trying to evaluate criteria within certain columns and if ALL the
data in the columns match against Sheet1 then input a value in Column
E. There can be nulls in the columns, row 4 for ex. My code below
doesn't seem to be working

Ex
Column E Column F Column G Column H Column Column J
1 Fruit Apple Bananan Orange Apple Orange
2 Steak Bananana
3 Fruit Orange
4 Fruit Apple
etc


Sheet 1
Column A
1 Fruit
2 Apple
3 Bananana
4 Orange
5 Peach
etc


lastrow = Cells(Rows.Count, 3).End(xlUp).Row

Dim mycell_Fruit As Range


For i = lastrow To 1 Step -1


For Each mycell_Fruit In
Sheets("Sheet1").Range("A1:A100").Cells.SpecialCells(xlCellTypeConstants)

If Cells(i, "F").Value = mycell_Fruit And Cells(i, "G").Value =
mycell_Fruit And Cells
(i, "H").Value = mycell_Fruit And Cells(i, "I").Value =
mycell_FruitAnd Cells(i, "J").Value =
mycell_Fruit Then

Cells(i, "E").Value = Sheets("Sheet1").Range("A1")

End If

Next mycell_Fruit

Next

 

[Tom Ogilvy]

lastrow = Cells(Rows.Count, 3).End(xlUp).Row

Dim mycell_Fruit As Range
with Worksheets("sheet1")
mycell_Fruit = .Range(.cells(1,1),.cells(rows.count,1).End(xlup))
End with

For i = lastrow To 1 Step -1
bMatch = True
for j = 6 to 10
if not isempty(cells(i,j)) then
res = Application.Match(cells(i,j),mycell_Fruit,0)
if iserror(res) then
bMatch = False
exit for
end if
end if
next
if bMatch then cells(i,"E").value = "Fruit"
Next

 

[mike.wilson8]

Tom,

I'm sure this we will work, but I'm getting an Object variable or With
block variable not set error on line -
mycell_Fruit = .Range(.cells(1,1),.cells(rows.count,1).End(xlup))