Find the cell using a found row cell and a found column cell

Question

I’m pulling data from one sheet onto another, using dated rows and titled columns.

Using find, i can find the date row and then want to offset to the column, placing data where the two intersect.

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

The code is bugging at

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

What i would like to happen is for the oFound row and oFound2 column to intersect and add 1 value to the current value.

AAA AAA · Accepted Answer · 2019-07-09T22:32:23

Replace the buggy line of code with this:

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

Find the cell using a found row cell and a found column cell

1 Answers