Replace ID Number with Customer Name using two arrays

[DynamiteHack]

I am using an array to populate a ListBox. I understand how to create an array and use it to populate a listbox with items from the Jobs table. I also have a customer table. The Jobs table contains the CustID number. How would I approach replacing the CustID number with their name from the Customer table?



Not necessarily looking for actual code, just a strategy. I have been stuck on this since yesterday and I am googled out!!

Thanks in advance!
https://cdn1.imggmi.com/uploads/2019/3/19/19985b09e335db4eab12c7d32d6bbcd8-full.png

 

[DynamiteHack]

Thanks for the reply, Hank. I'm trying to do as much of the work in memory as possible as I have the workbook hidden and the userform containing the listbox is all the user sees.

 

[DynamiteHack]

Thanks Steve! As for my comment about not wanting code, I didn't want to appear as not willing to do the work to figure this out. I am usually pretty good at piecing together google searches to come up with a solution. This particular issue seemed more difficult to find. I have to think it's done a lot. Perhaps it was poor query structure!!

I'll give this approach a go and see what happens...

Thanks again!

 

[Fluff]

Yo give you a rough idea on how to do it

Sub DynamiteHack()
   Dim Jary As Variant, Cary As Variant
   Dim Dic As Object
   Dim i As Long
   
   Set Dic = CreateObject("scripting.dictionary")
   Jary = Sheets("All").ListObjects("Tbljobs").DataBodyRange.Value2
   Cary = Sheets("All").ListObjects("Tblcust").DataBodyRange.Value2
   For i = 1 To UBound(Cary)
      Dic(Cary(i, 1)) = Cary(i, 2)
   Next i
   For i = 1 To UBound(Jary)
      Jary(i, 2) = Dic(Jary(i, 2))
   Next i
End Sub

 

[DynamiteHack]

You guys rock!! Thanks Fluff! I'll try that approach also. Always looking for new tricks!!

 

[DynamiteHack]

Welp, a half a dozen rabbit holes and a whole week later, I got it!!

I figured out how to accomplish my task by creating a third array where I hold the customer name to be called when I populate the listbox.

I decided against the table method (not that I figured it out) because I had to add a reference that I am afraid my end users wouldn't have and instructing them how to do it doesn't seem feasible. I may be wrong there. I really wanted to use arrays only. Here is what I came up with...

Private Sub CommandButton2_Click()Dim ws As Worksheet
Dim ed As Worksheet
Dim jobsTable As ListObject
Dim custTable As ListObject
Dim jobsArray As Variant
Dim custArray As Variant
Dim i As Long
Dim custNameArray As Variant




Call clearListBox


Application.ScreenUpdating = False
ThisWorkbook.Windows(1).Visible = True
    


Set ed = ThisWorkbook.Worksheets("Sheet1")
   


    'set path for table variable
        Set jobsTable = ed.ListObjects("tblJobs")
        Set custTable = ed.ListObjects("tblCust")
    
    'create array list from table
        jobsArray = jobsTable.DataBodyRange
        custArray = custTable.DataBodyRange


'loop through jobsArray, lookup customer number in custArray and return customer name to custNameArray
For i = LBound(jobsArray) To UBound(jobsArray)
    custNameArray = Application.VLookup(jobsArray(i, 2), custArray, 2, False)
    'Debug.Print custNameArray
     
    With Me.ListBox2
        .AddItem jobsArray(i, 1)
        .Column(1, .ListCount - 1) = custNameArray
        .Column(2, .ListCount - 1) = jobsArray(i, 3)
   End With

Next i

End Sub

Not sure if it's the best code ever but, it works pretty fast. I appreciate everyone's input!!
I learned a lot along the way! Thanks again!!

 

[Fluff]

Glad you sorted it & thanks for the feedback