Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluating criteria within certain columns
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.SpecialCel ls(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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluating criteria within certain columns
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 -- Regards, Tom Ogilvy " wrote: 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.SpecialCel ls(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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluating criteria within certain columns
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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Evaluating division by zero | Excel Worksheet Functions | |||
Evaluating mulitple criteria with COUNTIF | Excel Worksheet Functions | |||
Pivot table with added columns for evaluating results | Excel Worksheet Functions | |||
VBA for Evaluating Integrals | Excel Programming | |||
Evaluating Week No. | Excel Programming |