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

help adjusting code sum data in 2 sheets

0

hi, experts

i have data in sheet1  from a : e  and sheet2 from a: g  but what i  would  is sum  data 2 sheets and put thew result in sheet2  from L:O  it supposes sum sheet1 from b5 to the last data in e  with sheet2 from c4 to the last data in f  my code doesn't well work  you can check my code  and press macro button  then  you see the errors

 Sub AnyThing()
        Dim lastrow_1 As Long, counter As Long
        Dim lastrow_2 As Long, key As Variant
        Dim sh1 As Worksheet, sh2 As Worksheet
        Dim rng1, rng2 As Range, p As Variant
        Dim dict As Object
    Set sh1 = Sheets("SHEET1")
    Set sh2 = Sheets("SHEET2")
    sh2.Range("I3").Resize(1000, 3).ClearContents

    lastrow_1 = sh1.Cells(sh1.Rows.Count, "B").End(3).Row
    lastrow_2 = sh1.Cells(sh2.Rows.Count, "C").End(3).Row
    Set rng1 = sh1.Range("B5:E" & lastrow_1)
    Set rng2 = sh2.Range("C5:E" & lastrow_2)
    Set dict = CreateObject("Scripting.Dictionary")

    For Each p In rng1.Columns(2).Cells
        If Not dict.Exists(p.Value & "," & p.Offset(, 1)) Then
            dict.Add p.Value & "," & p.Offset(, 1), p.Offset(, 2)
        Else
            dict(p.Value & "," & p.Offset(, 1)) = _
            dict(p.Value & "," & p.Offset(, 1)) + p.Offset(, 2)
        End If
    Next p
   '===============================
       For Each p In rng2.Columns(2).Cells
        If Not dict.Exists(p.Value & "," & p.Offset(, 1)) Then
            dict.Add p.Value & "," & p.Offset(, 1), p.Offset(, 2)
        Else
            dict(p.Value & "," & p.Offset(, 1)) = _
            dict(p.Value & "," & p.Offset(, 1)) + p.Offset(, 2)
        End If
    Next p

    '==============================

   counter = 2
    With sh2
        For Each key In dict.Keys
             counter = counter + 1
            .Cells(counter, "K").Resize(1, 2) = Split(key, ",")
            .Cells(counter, "O") = dict(key)

        Next key

    End With
dict.RemoveAll: Set dict = Nothing
Set sh1 = Nothing: Set sh2 = Nothing
Set rng1 = Nothing: Set rng2 = Nothing
End Sub

Answer
Discuss

Answers

0

Imagine a dictionary more like the dictionaries you learned to use in school, or like a phone book where you look up a name and get a number, or you look up "kitab" and get the translation: "book". The value you look up is called the "Key", the return result is the "Value" linked to the "Key".

I'm not sure that Dict.Keys is a collection which you can loop through using For Each. I'm rather sure that a Key name can't contain a comma.

Note that the variable p in your first loop is a range. Declaring it as a Variant just opens the door to misunderstandings, such as what would be the effective difference between the range Columns(2) and the collection Columns(2).Cells. For Each p in Columns(2) will call up each cell in the column range. For Each p in Columns(2).Cells might do the same, or it might not. There is no telling what it does while p could be anything.

Discuss


Answer the Question

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