1

I'm trying to find the row of a cell (in a table) which meets 3 criteria. Thanks to another post I have come up with this working code:

x = Filter(Application.Transpose(Application.Evaluate("=IF((Sheet6!A6:A15=""joseph"")*(Sheet6!B6:B15>date(2001,1,1))*Sheet6!C6:C15=""grenade""),ROW(Sheet6!A6:A15),""x"")")), "x", False)*

I would now like to use named ranges in this code instead of "Sheet6!A6:A15). When I try this I keep getting an error "Type mismatch". I have tried to set different ranges such as:

1) Dim rng1 As Range Set rng1 = Worksheets("Sheet6").Range("A7:A15") x = Filter(Application.Transpose(Application.Evaluate("=IF((rng1=""joseph"")*(Sheet6!B6:B15>date(2001,1,1))*Sheet6!C6:C15=""grenade""),ROW(rng1),""x"")")), "x", False)* 2) Dim rng2 As Range Set rng2 = Worksheets("Sheet6").Range("TestTable10[Column1]") x = Filter(Application.Transpose(Application.Evaluate("=IF((rng2 =""joseph"")*(Sheet6!B6:B15>date(2001,1,1))*Sheet6!C6:C15=""grenade""),ROW(rng2 ),""x"")")), "x", False)* 3) Dim rng3 As Range Set rng3 = Worksheets("Sheet6").ListObjects("TestTable10").ListColumns(1).Range x = Filter(Application.Transpose(Application.Evaluate("=IF((rng3 =""joseph"")*(Sheet6!B6:B15>date(2001,1,1))*Sheet6!C6:C15=""grenade""),ROW(rng3 ),""x"")")), "x", False)*

I have also tried by doing the same for the ranges B6:b15 and C6:C15 but no matter what I do, I keep getting an error "Type Mismatch"

Thank you for your help!

Improve this question
4
  • What is stopping you from looping through the tables? Seems like a much more straight forward approach Commented May 20, 2015 at 15:58
  • To check for my 3 criteria or to set my ranges ? Either way I'm not sure how I would do that for 3 criteria in 3 columns (I'm just starting on VBA) Commented May 20, 2015 at 16:04
  • Do you have one criteria in each column? Are you looking for the row number that matches each criteria in its respective column? Commented May 20, 2015 at 16:13
  • I have one criteria per column (in the three first columns). So I'm trying to find the row in which the 3 criteria are met, that way I can access the correspondent cells in column 4 and column 5 Commented May 20, 2015 at 16:27

2 Answers 2

Reset to default
1 
Dim rng1 As Range Dim rRow as Range Dim matchedRow as Integer Set rng1 = Worksheets("Sheet6").Range("A7:A15") for each rRow in rng1 if Range("A" & rRow.row).Value2 = "Criteria1" and Range("B" & rRow.row).Value2 = "Criteria2" and Range("C" & rRow.row).Value2 = "Criteria3" then rRow.row = matchedRow ''Do stuff with matchedRow end if next rRow
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks a lot for your answer. When I run this, it tells me "wrong number of arguments or invalid property assignment" at the line "rRow.row = matchedRow" ?
0

Thank you so much kaybee99, the following code worked great for me:

 Dim Rng As Range Set Rng = Application.Range(Table[Column1]) Dim rRow As Range Dim matchedRow As Integer For Each rRow In BTUHRng If Worksheets(MySheet).Range("A" & rRow.Row).Value2 = Number1 And Worksheets(MySheet).Range("B" & rRow.Row).Value2 = "Text" And Worksheets(MySheet).Range("C" & rRow.Row).Value2 > Number2 Then matchedRow = rRow.Row Range("E1").Value = matchedRow Exit For End If Next rRow

I added an "Exit For" because I am only interested in the first value that meets these criteria.

Quick question: is it possible to replace "A", "B" and "C" by using a reference to the table column ? Something like Table[Column1].Column which would mean "A" ? THanks!

Improve this answer

2 Comments

You can use Range.column to return the column number. Then use Cells(rowNum, colNum) instead of range
No problem :) If my answer has helped you, you can mark it as correct by clicking the tick adjacent to it

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.