Find intersetion of column and row

I have dates in row 20 of my sheet, I have item numbers in colum a.  What I need to do is select the cell were the date in row 20 intersects with the item number in clumn A. The cell woudl be empty. It is set up similar to a pivot table.
Who is Participating?
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Try this code

Option Explicit

Sub Load()
Dim a As String
Dim b As Long
Dim c As Date
Dim d
Dim e
Sheets("BPCS Forecast File").Select
Do Until ActiveCell.Value = ""
'Load the item as variable a, qty as variable b, and date of week as variable c
    a = ActiveCell.Value
    b = ActiveCell.Offset(0, 1).Value
    c = ActiveCell.Offset(0, 3).Value
    ActiveCell.Offset(1, 0).Select
' check for more than one line with the same item and date. add to varible b if found
    Do While ActiveCell.Value = ActiveCell.Offset(-1, 0).Value And ActiveCell.Offset(0, 3).Value = ActiveCell.Offset(-1, 3).Value
             b = b + ActiveCell.Offset(0, 1).Value
             ActiveCell.Offset(1, 0).Select
'loacte the correct date
    Sheets("Forecast Detail").Select
'    Range("b1").Select
'        Do Until ActiveCell.Value = c Or ActiveCell.Value = ""
'            If ActiveCell.Value <> c Then
'                ActiveCell.Offset(0, 1).Select
'            End If
'        Loop
'find the item number
'        Do Until Cells(Application.ActiveCell.Row, 1).Value = a Or Cells(Application.ActiveCell.Row, 1).Value = ""
'                               ActiveCell.Offset(1, 0).Select
'        Loop
Cells(Range("A:A").Find(a).Row, Range("1:1").Find(c).Column).Select

        ActiveCell.Value = b
       Sheets("BPCS Forecast File").Select

End Sub

Open in new window

Saqib Husain, SyedEngineerCommented:
Have not tried it but it should be something like

mlsbejAuthor Commented:
Thnaks for teh reply, I tried the code but I received a runtime error 91 " Object variable or with block variable not set.  I substituted my item number variable and date bariable into teh code.
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Saqib Husain, SyedEngineerCommented:
Can you upload a sample for testing?
mlsbejAuthor Commented:
Here is an exceprt from the actual file and it also contains my code so far. My actual file has has 70,000 rows of data it is pulling from on the BPCS Forecast File tab. The Forecast File tab I am putting that data into has 9,194 rows of items with 52 weeks. My code I have works but it takes several hours to work through all of the cells to find the correct one to put the qty into for each item and week. I know there is a faster way to do what I am doing but after several days of research and trying everything I can think of I am at a loss.
mlsbejAuthor Commented:
This was exactly what I have been looking for it speeds up the macro dramatically and does so with less code. I can aeasily adapt this to other projects as well. Thank you for your help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.