1

I was hoping to be able to have a macro that would be able to extract the ID from each of our orders and put them into table 3.

for example

VBasic2008
  • 44,888
  • 5
  • 17
  • 28
Austin Ramos
  • 27
  • 4
  • why not just index/match or vlookup? is macro really necessary? – kpark Feb 13 '22 at 21:27
  • In `G2` use `=IF(Table1[@[Table 1]]="","",Table1[@[Table 1]])`, in `H2` use `=IFERROR(INDEX(Table2[ID],MATCH([@[Table 3 (RESULTS)]],Table2[Table 2],0)),"")`. – VBasic2008 Feb 13 '22 at 22:55
  • I had a versions that was relaying on Index and match. However, it became difficult once I started add more rules. So I decided to have a macro perform that task, so I can add more If statements while keeping the same loops. – Austin Ramos Feb 14 '22 at 02:25
  • Then tell me am I to use both tables or only the second table to get the results in the third table? Are the tables on different worksheets? – VBasic2008 Feb 14 '22 at 02:28
  • This is a small step in a large marco that I am working on for work so I can't show the entire project. – Austin Ramos Feb 14 '22 at 02:32
  • The 3td table would be on a different sheet but If I can get the loops correct than I can add the correct sheet I need to paste it on after the fact. – Austin Ramos Feb 14 '22 at 02:33
  • To conclude, do you want all the table1 values in the 1st column and the respective ID values in the 2nd column of an existing table on another worksheet? – VBasic2008 Feb 14 '22 at 02:37
  • It would be both tables. Table 1 is the orders we received and Table2 to is the total data of all orders. Table 3 would be Table1's data but with the added data from Table 2. – Austin Ramos Feb 14 '22 at 02:42
  • Yes that is correct – Austin Ramos Feb 14 '22 at 02:42

1 Answers1

0

Lookup Data (Excel Tables)

Option Explicit

Sub LookupData()
       
    Const lName As String = "Sheet1"
    Const ltName As String = "Table1"
    Const lcName As String = "Table 1"
    
    Const sName As String = "Sheet1"
    Const stName As String = "Table2"
    Const sclName As String = "Table 2"
    Const scvName As String = "ID"
    
    Const dName As String = "Sheet2"
    Const dtName As String = "Table3"
    Const dclName As String = "Table 3 (RESULTS)"
    Const dcvName As String = "ID"
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Lookup
    Dim lws As Worksheet: Set lws = wb.Worksheets(lName)
    Dim ltbl As ListObject: Set ltbl = lws.ListObjects(ltName)
    Dim lrCount As Long: lrCount = ltbl.Range.Rows.Count
    Dim lcl As ListColumn: Set lcl = ltbl.ListColumns(lcName) ' Lookup Column
    
    ' Source
    Dim sws As Worksheet: Set sws = wb.Worksheets(sName)
    Dim stbl As ListObject: Set stbl = sws.ListObjects(stName)
    Dim scl As ListColumn: Set scl = stbl.ListColumns(sclName)
    Dim slrg As Range: Set slrg = scl.DataBodyRange ' Lookup Column
    Dim scv As ListColumn: Set scv = stbl.ListColumns(scvName)
    Dim svrg As Range: Set svrg = scv.DataBodyRange
    Dim svData As Variant: svData = svrg.Value ' Value Array
    
    ' Destination
    Dim dws As Worksheet: Set dws = wb.Worksheets(dName)
    Dim dtbl As ListObject: Set dtbl = dws.ListObjects(dtName)
    Dim drCount As Long: drCount = dtbl.Range.Rows.Count
    Dim dcl As ListColumn: Set dcl = dtbl.ListColumns(dclName) ' written to
    Dim dcv As ListColumn: Set dcv = dtbl.ListColumns(dcvName) ' written to
    
    ' Copy lookup column.
    dcl.DataBodyRange.Resize(lrCount - 1).Value = lcl.DataBodyRange.Value
    
    Dim lData As Variant: lData = lcl.DataBodyRange.Value ' Lookup Array
    Dim dvData As Variant: ReDim dvData(1 To lrCount - 1, 1 To 1) ' Value Array
    
    Dim sIndex As Variant
    Dim r As Long
    
    ' Match value data.
    For r = 1 To lrCount - 1
        sIndex = Application.Match(lData(r, 1), slrg, 0)
        If IsNumeric(sIndex) Then
            dvData(r, 1) = svData(sIndex, 1)
        End If
    Next r
    
    ' Copy value array to value range.
    dcv.DataBodyRange.Value = dvData
    
    If lrCount < drCount Then
        ' Resize and clear.
        dtbl.Resize dtbl.Range.Resize(lrCount)
        dtbl.DataBodyRange.Resize(drCount - lrCount).Offset(lrCount - 1).Clear
    End If
    
End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28