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!

Community
  • 1
  • 1
Ettelaiv
  • 115
  • 1
  • 1
  • 7
  • What is stopping you from looping through the tables? Seems like a much more straight forward approach – kaybee99 May 20 '15 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) – Ettelaiv May 20 '15 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? – kaybee99 May 20 '15 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 – Ettelaiv May 20 '15 at 16:27

2 Answers2

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
kaybee99
  • 4,566
  • 2
  • 32
  • 42
  • 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" ? – Ettelaiv May 20 '15 at 17:00
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!

Ettelaiv
  • 115
  • 1
  • 1
  • 7
  • 1
    You can use `Range.column` to return the column number. Then use `Cells(rowNum, colNum)` instead of `range` – kaybee99 May 20 '15 at 18:56
  • No problem :) If my answer has helped you, you can mark it as correct by clicking the tick adjacent to it – kaybee99 May 20 '15 at 20:29