excel - 使用找到的行单元格和找到的列单元格查找单元格

Question

我使用日期行和标题列将数据从一张纸拉到另一张纸上。

使用查找，我可以找到日期行，然后想要偏移到列，将数据放置在两者相交的位置。

For i = 3 To lr

Set oSheet1 = ThisWorkbook.Worksheets("Raw Data")
Set oSheet2 = ThisWorkbook.Worksheets(Worksheets("Raw     Data").Cells(i, 5).Value)

 Set oLookFor = oSheet1.Cells(i, 4)

 Set oFound = oSheet2.Range("B:B").Find(what:=oLookFor.Text,       LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)

 If Not oFound Is Nothing Then
     If oSheet1.Cells(i, 1).Value = "Standard Attendance" Then
       oFound.Offset(0, 2).Value = oFound.Offset(0, 2).Value + 1
     ElseIf oSheet1.Cells(i, 1).Value = "Allowance" Then
       Set oLookCol = oSheet1.Cells(i, 2)
       Set oFound2 = oSheet2.Range("B4:P4").Find(what:=oLookCol.Text,LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)
        If Not oFound2 Is Nothing Then
          Set j = oFound2.Column
          oFound.Offset(0, j).Value = oFound.Offset(0, j).Value + 1

         Else
          MsgBox "Nil"
       End If

  End If
  End If
  Next i

代码在窃听

oFound.Offset(0, j).Value = oFound.Offset(0, j).Value + 1

我想要发生的是 oFound 行和 oFound2 列相交并将 1 值添加到当前值。

Answer 1

用以下代码替换错误的代码行:

With Intersect(OFound.EntireRow, OFound2.EntireColumn)
  .Value = .Value + 1
End With