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.
mlsbejAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

cells(range("A:A").find(itemno).row,range("20:20").find(givendate).column).select
0
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.
0
Saqib Husain, SyedEngineerCommented:
Can you upload a sample for testing?
0
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

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.
Test-File.xls
0
Saqib Husain, SyedEngineerCommented:
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
Range("A2").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
    Loop
'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
Loop




End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.