if 语句 - Excel VBA 中的语法：如何在 "=IF" 函数中将表的列引用为范围

Question

我试图找到符合3个标准的单元格行(在表中)。多亏了另一个帖子，我想出了这个工作代码:

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)*

我现在想在这段代码中使用命名范围，而不是"Sheet6!A6:A15"。当我尝试这样做时，我一直得到一个错误"类型不匹配"。我尝试设置不同的范围，如:

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)*

我也尝试过为B6:b15和C6:C15范围做同样的事情，但无论我做什么，我总是得到一个错误"类型不匹配"

谢谢你的帮助!

Answer 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

Answer 2

非常感谢kaybee99，下面的代码对我来说工作得很好:

    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

我添加了一个"Exit For"，因为我只对满足这些条件的第一个值感兴趣。

快速问题:是否有可能通过使用对表列的引用来替换"A"，"B"one_answers"C"?比如Table[Column1]。这一栏指的是A谢谢!