Run-Time error '1004' Application-defined or object-defined error'

Question

I have a macro that is supposed to help me transform many to many relationships into many to one relationships.

For example, if I have a SKU, and an order to a certain country attached to that SKU, and then a re-currence of that same country/SKU combination, I want to create a line by line table that contains JUST the SKU, and then in the neighboring cell a comma separated list of values of all the countries that the has sold in. I am getting a Run-time Application error on this. I do not know why.

Can someone please take a look at this and help me out when they have a moment?

I have added a couple of stars and errors, indicating where the error occurs.

Sub SteveOranjin()
    Dim Cl As Range
    '''This is all in VBA for EXCEL:
    With CreateObject("scripting.dictionary")
        For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
            If Not .exists(Cl.Value) Then
                .Add Cl.Value, Cl.Offset(, 1).Value
            Else
                .Item(Cl.Value) = .Item(Cl.Value) & ", " & Cl.Offset(, 1).Value
            End If
        Next Cl
        Range("F2").Resize(.Count, 2).Value = Application.Transpose(Array(.keys, .items))  ' ***[error here.]***
    End With
End Sub

works for me. BTW use a variable for last row and then use that. Range("A" & Rows.Count).End(xlUp) gets calculated everytime the loop runs ;) — Siddharth Rout
– Siddharth Rout, Commented Feb 27, 2020 at 5:13
How could it be that you're not getting a run time error and I am? — Stephen Smith
– Stephen Smith, Commented Feb 27, 2020 at 5:17

Siddharth Rout · Accepted Answer · 2020-02-27 06:17:58Z

1

Is this what you are trying?

Option Explicit

Sub SteveOranjin()
    Dim ws As Worksheet
    Dim lRow As Long, i As Long
    Dim dict As Object
    Dim arKey, arItm, arFinal

    Set ws = Sheet1 '<~~ Change this to the relevant sheet   
    Set dict = CreateObject("scripting.dictionary")

    With ws
        .Columns("A:B").RemoveDuplicates Columns:=Array(1, 2)

        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        For i = 2 To lRow
            If Not dict.exists(.Range("A" & i).Value) Then
                dict.Add .Range("A" & i).Value, .Range("B" & i).Value
            Else
                dict.Item(.Range("A" & i).Value) = dict.Item(.Range("A" & i).Value) & _
                                                   ", " & .Range("B" & i).Value
            End If
        Next i
    End With

    arKey = dict.Keys: arItm = dict.Items

    ReDim arFinal(LBound(arKey) To UBound(arKey), 0 To 1)

    For i = LBound(arKey) To UBound(arKey)
        arFinal(i, 0) = arKey(i): arFinal(i, 1) = arItm(i)
    Next i

    Range("F2").Resize(UBound(arFinal) + 1, 2) = arFinal
End Sub

In Action

edited Feb 27, 2020 at 6:17

answered Feb 27, 2020 at 5:56

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Stephen Smith Over a year ago

It's producing a lot of duplicates. Thats close. So it should come out as 6.813.104 | Vietnam, Italy, Serbia, Belgium, Portugal, Isreal

Siddharth Rout Over a year ago

Yes. That is because you are not taking care of it. If you try your original code on a small dataset, you will see that it will create duplicates. The easiest way would be to use Data Tab | Remove Duplicates and then run the macro. You can also do it via code by adding the line Columns("A:B").RemoveDuplicates Columns:=Array(1, 2) Put the line .Columns("A:B").RemoveDuplicates Columns:=Array(1, 2) before lRow = .Range("A" & .Rows.Count).End(xlUp).Row in the above code.

Stephen Smith Over a year ago

I tried the "Data Tab | Remove Duplicates" method, and it is still showing multiple instances of Italy, for 6.813.104, even though there is only one instance Italy for 6.813.104 after all of the duplicates have been removed.

Stephen Smith Over a year ago

It doesn't like that, I get the same error again. The one that prompted this post.

Siddharth Rout Over a year ago

I updated the answer. You may have to refresh it to see it. it works. If you are still facing a problem. Close the workbook and reopen it and try it again

|

Collectives™ on Stack Overflow

Run-Time error '1004' Application-defined or object-defined error'

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related