Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Loop between two tables to sum their matches

0

Greetings, I am currently struggling with this code, you will see that you must find the matches through a loop between "Sourcetable" and "GoalTable" according to their ID, and add the values of the products that match, I am trying to apply the "Val function" but it does not work well, I am attentive to any contribution

Option Explicit


Sub CopyData()
    Dim SrcTable    As ListObject, GoalTable As ListObject
    Dim SourceArr   As Variant, ID As Variant, m As Variant
    Dim i           As Long
   
    With ThisWorkbook
        Set SrcTable = .Worksheets("SourceData").ListObjects("Source")
        Set GoalTable = .Worksheets("GoalTable").ListObjects("Goal")
    End With
   
    SourceArr = SrcTable.DataBodyRange.Value
   
    For i = 1 To UBound(SourceArr, xlRows)
        ID = SourceArr(i, 1)
        m = Application.Match(ID, GoalTable.ListColumns("ID").DataBodyRange, 0)
        If Not IsError(m) Then
            With GoalTable
                .DataBodyRange(CLng(m), 3).Value = SourceArr(i, 3)
                .DataBodyRange(CLng(m), 4).Value = SourceArr(i, 4)
            End With
        End If
    Next i
   
End Sub
Answer
Discuss

Discussion

Kapela

Please attach an Excel file to your question- it really helps us by saving time. 
John_Ru (rep: 6092) Nov 10, '22 at 10:00 am
Kapela

Tried your code on a file I created and it successfully transfers the IDs/  imaginary scores I added to my Source table.

Which items do you want to add? Please edit your question to expand (and add an Excel, file as I requested above).
John_Ru (rep: 6092) Nov 15, '22 at 6:54 am
So did you try my answer? 
John_Ru (rep: 6092) Nov 23, '22 at 3:43 am
Add to Discussion

Answers

0

Kapela

Your question isn't quite clear but if you want to add values from SourceData to matching totals (already in your Goal table), I suggest you replace your lines:

m = Application.Match(ID, GoalTable.ListColumns("ID").DataBodyRange, 0)
        If Not IsError(m) Then
            With GoalTable
                .DataBodyRange(CLng(m), 3).Value = SourceArr(i, 3)
                .DataBodyRange(CLng(m), 4).Value = SourceArr(i, 4)

with these (changes in bold):

m = Application.Match(ID, GoalTable.ListColumns("ID").DataBodyRange, 0)
If Not IsError(m) Then
        m = CLng(m)
       With GoalTable 
              ' add to existing values
               .DataBodyRange(m, 3).Value = .DataBodyRange(m, 3).Value + SourceArr(i, 3)
               .DataBodyRange(m, 4).Value = .DataBodyRange(m, 4).Value + SourceArr(i, 4)

Hope this helps. If so, please mark Answer as Selected. If not, please attach a file and clarify your question.

Discuss

Discussion

Hi Kapela. Did that work? 
John_Ru (rep: 6092) Dec 7, '22 at 1:45 am
One month and still no reply :(
John_Ru (rep: 6092) Dec 15, '22 at 7:24 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login