How to compare text of a column with text in a column of different workbook?

Question

I want to check if contents of column A in one workbook is equal to contents of column A of another workbook.

Cell by cell they should be same.

The best I could find is:

Sub RowCompare()
    Dim ary1() As Variant
    Dim Range1 As Range, Range2 As Range, rr1 As Range, rr2 As Range
    Set Range1 = Range("B9:F20")
    Set Range2 = Range("I16:M27")
    Set rr1 = Range1.Rows(5)
    Set rr2 = Range2.Rows(5)
    ary1 = Application.Transpose(Application.Transpose(rr1))
    ary2 = Application.Transpose(Application.Transpose(rr2))
    st1 = Join(ary1, ",")
    st2 = Join(ary2, ",")
    If st1 = st2 Then
        MsgBox "the same"
    Else
        MsgBox "different"
    End If
End Sub

No this code is something I got from google, I was trying to make my code around it. My question is primary thing to focus, Range is static. — Palash Gangal, Oct 08 '18 at 14:33

score 0 · Answer 1 · answered Oct 08 '18 at 14:33

0

You would want to specify the Workbook and Worksheet before the Range when setting Range1 And Range2

For example, make this edit:

Set Range1 = Workbooks("workbook 1 name").Worksheets("your sheet name").Range("B9:F20")
Set Range2 = Workbooks("workbook 2 name").Worksheets("other sheet name").Range("I16:M27")

Also, I see you grabbed this from Fastest way to check if two ranges are equal in excel vba

answered Oct 08 '18 at 14:33

Kubie

1,551
3
12
23

Hi, Yes I did took this code from the link you shared. – Palash Gangal Oct 08 '18 at 14:34
Yes I am able to define the range1 and range2 from there respective sheets, But I am not sure what rest of the code is doing, Mainly from "ary1 = " – Palash Gangal Oct 08 '18 at 14:35
@PalashGangal The code after `ary1` basically turns the ranges into strings and compares if they are equal by setting `string1 = string2`. The answer by @urdearboy covers your problem so I'd suggest looking at that – Kubie Oct 08 '18 at 14:42
Yes answer by urdearboy work properly for me, I am now trying to integrate it in my code. Now just that I have to learn how to call a macro inside a macro, else my primary code will be very big. If that is possible at all. – Palash Gangal Oct 08 '18 at 14:47
For some reason he deleted his code. Maybe he is editing something in that. – Palash Gangal Oct 08 '18 at 14:50
Yes just make a separate `Sub macroName` for your second macro and then from within the first macro you can call it with just `macroName` on its own line or `Call macroName` ... both work – Kubie Oct 08 '18 at 14:50
You can use the code you have just remove the 2 lines with `Rows(5)` in them and change the `Ranges` to your desired column ranges – Kubie Oct 08 '18 at 14:54
Yes I am able to call the macro, thanks. But now I have another issue. I am having urdearboy's code (Pasted below). In that I want to assign ws1 and ws2 from workbook I have open in my Primary code. Since it opens many workbook one by one and I want to check this in every workbook. – Palash Gangal Oct 09 '18 at 07:26

score 0 · Answer 2 · answered Oct 09 '18 at 07:30

Sub CompareColumns()

Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Sheet1")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Sheet2")
Dim Arr1 As Variant, Arr2 As Variant
Dim i As Long, Same As Boolean

'Assume they are the same before starting loop
Same = True

'Assign some range to Array
 Arr1 = ws1.Range("A1:A20").Value
 Arr2 = ws2.Range("A1:A20").Value

 'Loop through Array
 For i = LBound(Arr1) To UBound(Arr2)
      If Arr1(i, 1) <> Arr2(i, 1) Then
      Same = False
  End If
  Next i

  'Check the value of "Same"
  If Same = False Then MsgBox "Ranges are not equal!"


  End Sub

@kubie In this code I want to assign sheets for ws1 and ws2 from primary code. Any idea how to do that? I don't want to in-built this whole code in my primary code. — Palash Gangal, Oct 09 '18 at 07:32

How to compare text of a column with text in a column of different workbook?

2 Answers2