asked on

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.

Saqib Husain

Have not tried it but it should be something like

cells(range("A:A").find(itemno).row,range("20:20").find(givendate).column).select

mlsbej

ASKER

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.

Saqib Husain

Can you upload a sample for testing?

mlsbej

ASKER

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

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mlsbej

ASKER

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.