If函数用于检查多个表中的多个列条件

Question

我正在开始收集每周数据的Excel表，我试图创建一个if语句来访问工作簿中的最后一个(索引)表并检查多个条件。

我需要检查k列中的单元格是否为"true";如果是，那么J列中的日期是否在名为"Dashboard"的工作表的月份范围内。如果满足这两个条件，我将在计数器中添加一个，然后最终将此计数器编号打印到Dashboard工作表的单元格中。

到目前为止我的代码是:

Sub CreateTable()
Dim n As Integer, RangeCount As Integer
Dim MaxVal As Long
n = 0
RangeCount = 1
sheets(sheets.Count).Select) 'I don't want this line but I don't know how else to access the final sheet in code
MaxVal = WorksheetFunction.CountA(Range("J1:J14")) 'Columns J and K will always be the same length
For RangeCount = 1 to MaxVal
If Cells(RangeCount, 11) = "true" And Sheets("Dashboard").Range("Y17") <= sheets(sheets.Count).Select.Cells(RangeCount, 11) And sheets("Dashboard").WorksheetFunction.EoMonth(Range("Y17", 0)) >= Cells(RangeCount, 11) Then
n = n + 1
End If
Next RangeCount
End Sub

Y17是仪表板中列出月份的单元格(1/12/2022)。我目前的错误是Object Required，但我认为我已经定义了我需要定义的一切，等等。任何帮助将不胜感激!

Answer 1

你对你的条件有什么问题吗?

Option Explicit
Sub CreateTable()
Dim n As Integer, RangeCount As Integer
Dim MaxVal As Long
n = 0
RangeCount = 1
Sheets(Sheets.Count).Select                         'I don't want this line but I don't know how else to access the final sheet in code
MaxVal = WorksheetFunction.CountA(Range("J1:J14"))  'Columns J and K will always be the same length
For RangeCount = 1 To MaxVal
If CheckConditions(RangeCount) Then
n = n + 1
End If
Next RangeCount
End Sub

Function CheckConditions(locCnt As Integer) As Boolean    
Dim locTest As Boolean
'which worksheet? do you want the activesheet here?
locTest = Cells(locCnt, 11) = "true"  
locTest = locTest And Sheets("Dashboard").Range("Y17") <= Sheets(Sheets.Count).Cells(locCnt, 11)
'which worksheet for cells? do you want the activesheet here?
locTest = locTest And Sheets("Dashboard").WorksheetFunction.EoMonth(Range("Y17", 0)) >= Cells(locCnt, 11)
CheckConditions = locTest
End Function