Thread: Find and Insert Intersecting Values

Hello BDB,

I wrote this macro based on what you posted. I am not sure this what you intended because I saw several ways the data could be interpreted. At the very least, it is a starting point. A button has been added to the "Non Completed" sheet to run macro, which is shown below.

Option Explicit

Public Jobs As Object
Public Parts As Object

Sub LoadPartsAndJobs()

  Dim C As Long
  Dim ColRng As Range
  Dim Item As String
  Dim Key As String
  Dim R As Long
  Dim RngEnd As Range
  Dim RowRng As Range
  Dim Wks As Worksheet
  
    Set Wks = Worksheets("PartNumVsJobNum")
    
    Set RowRng = Wks.Range("A3")
    Set ColRng = Wks.Range("B1")
    
    Set Jobs = Nothing
    Set Parts = Nothing
    
      Set Parts = CreateObject("Scripting.Dictionary")
      Parts.CompareMode = vbTextCompare
      
      Set Jobs = CreateObject("Scripting.Dictionary")
      Jobs.CompareMode = vbTextCompare
      
      Do
        Key = Trim(RowRng.Offset(R, 0))
        If Key <> "" Then
          If Not Parts.Exists(Key) Then
             Item = RowRng.Offset(R, 0).Row
             Parts.Add Key, Item
             R = R + 1
          End If
        Else
          Exit Do
        End If
      Loop
      
      Do
        Key = Trim(ColRng.Offset(0, C))
        If Key <> "" Then
          If Not Jobs.Exists(Key) Then
             Item = Split(ColRng.Offset(0, C).Address(True, False), "$")(0)
             Jobs.Add Key, Item
             C = C + 1
          End If
        Else
          Exit Do
        End If
      Loop
      
End Sub

Sub Macro1A()

  Dim Job As Range
  Dim JobNums As Range
  Dim PartNum As String
  Dim Qty As Variant
  Dim RngEnd As Range
  Dim Wks As Worksheet
  
    LoadPartsAndJobs
    
    Set Wks = Worksheets("Non_Completed")
    
    Set JobNums = Wks.Range("$E$2")
    Set RngEnd = Wks.Cells(Rows.Count, JobNums.Column).End(xlUp)
    Set JobNums = IIf(RngEnd.Row < JobNums.Row, JobNums, Wks.Range(JobNums, RngEnd))
    
      For Each Job In JobNums.Cells
        PartNum = Trim(Wks.Cells(Job.Row, "A"))
        Qty = Trim(Wks.Cells(Job.Row, "G"))
        Worksheets("PartNumVsJobNum").Cells(Parts(PartNum), Jobs(Trim(Job))) = Qty
      Next Job
      
End Sub

Leith, I've figured out how to adjust the macro to run with the part numbers in column B on sheets "PartNumVsJobNum". But after, inserting the worksheet into my actual workbook and placing the macro's into module 2 of the workbook, trying to run the macro's produces an Run-Time error'1004':application defined or object defined error. On debug, the following
line gets highlighted:

Worksheets("PartNumVsJobNum").Cells(Parts(PartNum), Jobs(Trim(Job))) = Qty

I tried several tricks to try and isolate the issue. First, I tried using the unadulterated sheet from the working workbook you sent me.
Next, I created a new workbook where I inserted the non_completed sheet from my actual workbook and the PartNumVsJobNum from your sent workbook,
and Lastly replaced the non_completed sheet of the workbook you sent with the actual non_completed sheet, but to no avale, all attempts failed.

I even tried adding ....

      Set Parts = CreateObject("Scripting.Dictionary")
      Parts.CompareMode = vbTextCompare
      
      Set Jobs = CreateObject("Scripting.Dictionary")
      Jobs.CompareMode = vbTextCompare

...to macro1a, which didn't help.

Any help fixin' this error is appreciated.

thnx.

BDB

Hello BDB,

Looks like I have some catching up to do. Can you post a copy of your current workbook?

Hello BDB,

Maybe you aren't running the correct macro. I ran "Transpose" then ran "Macro1A" - no errors. The table looks fine.

EDIT: Just checked the macro button. It is calling the correct macro.

Hello BDB,

Am I looking for an error code or something in the data that is incorrect?

Yes, It will error.
...just add a new job # to the Non_Completed page and see what happens.
Put in anything you like. example: 4TR or 9ZW in the job #column again
it will go on sheet("Non_Completed").

Thanks,

BDB

Hi BDB
I've followed this thread from the beginning and am amazed at the code Mr Ross developed for your issue. I understand you still have a problem

Add a fictitious job number to the Non_Completed sheet and run.
It will error.

My question is "What do you wish to happen if a non-existent job number exists on the Non_Completed sheet". Do you want

fixed so that i may have jobs numbers on the non_Completed sheet that will not be found on the "PartNumVsJobNum" sheet

or do you want a test to make certain it does exist as a legitimate job number? Either can be done, just let me know what you wish to happen and I'll take a look at it for you.
J

Hello bdb1974,

This latest workbook is very different in layout. To make this even more difficult you have included hyperlinks. I have no idea what data you want where now. I need more information than "It's broke. Can you fix it?"