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