1

I am trying to compare two columns in different worksheets to check if value in column A exist in column B.

When I am trying to transpose row value and store it into dictionary for comparison, run-time error '13' is triggered.

Here is the code:

Sub Compare()

Dim rngA As Range, rngB As Range
Dim dict As Object, rw As Range
Dim a As Application, tmp As String
Dim Row1Last As Long
Dim Row2Last As Long

Set a = Application
Set dict = CreateObject("scripting.dictionary")
Set sht1 = Worksheets("list_Facility_BOS")
Set sht2 = Worksheets("List_Facility_PG")


With Sheets("list_Facility_BOS")
Row1Last = .Cells(Rows.Count, "G").End(xlUp).Row
End With

With Sheets("List_Facility_PG")
Row2Last = .Cells(Rows.Count, "H").End(xlUp).Row
End With

Set rngA = sht1.Range("G2:G" & Row1Last)
Set rngB = sht2.Range("H2:H" & Row2Last)

For Each rw In rngA.Rows
    dict.Add Join(a.Transpose(a.Transpose(rw.Value)), Chr(0)), rw.Row
Next rw

For Each rw In rngB.Rows
    tmp = Join(a.Transpose(a.Transpose(rw.Value)), Chr(0))
    If dict.exists(tmp) Then
    Else
        rw.Cells(1).Interior.ColorIndex = 3
    End If
Next rw

End Sub

I attempt to compare a 20k rows record column against another 20k rows column in another worksheet. Error '13' was trigger in this line of code:

dict.Add Join(a.Transpose(a.Transpose(rw.Value)), Chr(0)), rw.Row

I am new to excel VBA programming, sorry if I could'nt explain it clearly. Please let me know if there is any mistakes in my code.

YowE3K
  • 23,852
  • 7
  • 26
  • 40
Dvid Lou
  • 11
  • 5
  • Transpose will fail if there are more rows than excel has possible columns... that's all ;) – Dirk Reichel Aug 26 '17 at 18:30
  • @DirkReichel, are you sure? This seem to work [fastest-way-to-find-a-row-in-excel-range-using-excel-macro-vba](https://stackoverflow.com/questions/22685622/fastest-way-to-find-a-row-in-excel-range-using-excel-macro-vba/22704708#answer-22704708). @Dvid Maybe more than 255 chars in a cell [vba-scripting-dictionary-run-time-error-13-type-mismatch](https://stackoverflow.com/questions/17549379/vba-scripting-dictionary-run-time-error-13-type-mismatch#answer-17817522)? If not show the value of `rw.value` at error. – BitAccesser Aug 26 '17 at 19:54
  • @BitAccesser haven't seen the `For Each rw In rng.Rows` part... this way the 2^16 limit will never be hit... but having just one column, `rw` will only be one cell which cannot be transposed at all as `rw.Value` isn't an array. pretty obvious if reading the whole code... (I should do this more often) :P – Dirk Reichel Aug 26 '17 at 20:25
  • the two `with ...` blocks add extra lines that do not need to be there ... just use this for the first one ... similar to the other one ..... `Row1Last = sht1.Cells(Rows.Count, "G").End(xlUp).Row` – jsotola Aug 27 '17 at 00:00
  • the variable name `Row1Last` implies that it means the last (maybe last used) cell in a particular row.... variable name `colGLast` or `colG_last`, meaning last cell in column G, is more descriptive.... or 'lastBOSrow' meaning last row on BOS sheet – jsotola Aug 27 '17 at 00:11
  • @BitAccesser Thank you for your reply. The value where it hits the error is "1020001TL200000", is there any problem with the value? Thanks – Dvid Lou Aug 27 '17 at 12:45

1 Answers1

0

The explanation for the function got lost, while copied several times. This is the original I think fastest-way-to-find-a-row-in-excel-range-using-excel-macro-vba.

Have a look at the comments

For Each rw In rngA.Rows
    'Create a key from the entire row and map to row
    ' If your rows are not unique you'll have to do a bit more work here
    ' and use an array for the key value(s)
    dict.Add Join(a.Transpose(a.Transpose(rw.Value)), Chr(0)), rw.Row
Next rw

You need unique rows in rngA or you get an array, that causes error 13. See Using-the-Dictionary-Class-in-VBA

That kind of problems can be avoided, using a database instead of an Excel-File.

BitAccesser
  • 719
  • 4
  • 14