Compare Cells in Different Workbooks

Sub Find_Tag()

' This script compares a specified data sheet against the OURS data sheet, and finds discrepancies
' based on column headings.
' Project Creator: XXX
' Date Created: 5/9/2012

' The user of this script must create 3 sheets to be used by the script, in the workbook where the variable
' worksheet resides. These sheets must then have their names stored in the variables
' s_WS_SheetName, s_WS_FileName, and s_WS_DataSheetName.
' The user of this script must also ensure that in the worksheet being used to compare
' against OURS.xlsx, it must contain headings with the same heading names. Heading locations
' do not matter. There must also be a column heading named PERMANENT_TAG_NUMBER in order
' to identify which rows in s_WS_SheetName goes to which row in OURS.

' In order to use this script effectively, there must be two workbooks opened, one of which must be named ' OURS.xlsx and have a sheet named Sheet1 to be compared to.
' The next workbook can be whatever you want to compare against OURS workbook, Sheet1 worksheet.
' In the next workbook, there must be two sheets, one called Data_Sheet for acting as temporary storage
' and Final Sheet, where the discrepancies are output.

' Modified 5/21/2012 by Tom Shallenberger
' Added s_WS_tag_col_num and char in order to make it easier to obtain
' col letter and number for the PERM_TAG_NUM col location.



Dim s_WS_Tag, s_ours_address As String
Dim s_OURS_row, s_WS_row, s_OURS_col, s_WS_col As String
Dim OURS_string, s_WS_string As String

Dim s_WS_Filename, s_WS_SheetName, s_WS_DataSheetName, s_WS_tag_col As String
Dim i_WS_colCount, i_WS_rowCount As Integer

' **************************************************
s_WS_Filename = "Wendy.xls"                         ' Remember to fill these
s_WS_SheetName = "Outside DC Scope"                 ' in with the right names!
s_WS_DataSheetName = "Data_Sheet"                 ' This doesn't need to be changed unless needed
s_WS_tag_col = "PERMANENT_TAG_NUMBER"             ' This is what identifies the rows
' **************************************************

Dim s_WS_tag_col_num, s_WS_tag_col_char As String

Dim c As Range
Dim testchar As String
Dim s_WS_row_one, OURS_row_one_s As String
Dim OURS_row_a, v_WS_Array As Variant
Dim concat_string As String

Dim Test_Compare As Integer

i_WS_colCount = 0
i_WS_rowCount = 0
i_WS_colCount = Workbooks(s_WS_Filename).Sheets(s_WS_SheetName).UsedRange.Columns.Count     ' assigns the number of
i_WS_rowCount = Workbooks(s_WS_Filename).Sheets(s_WS_SheetName).UsedRange.Rows.Count        ' col and rows to integers.



For i = 2 To i_WS_colCount ' going from the 2d col over, searching the columns in the first
                ' row and storing the corresponding col letters
            '*****************************************************

            ' Assign the values, one at a time, in DC to be verified row 1 col i to wendy-row_s
            s_WS_row_one = Workbooks(s_WS_Filename).Sheets(s_WS_SheetName).Cells(1, i).Value
            Workbooks(s_WS_Filename).Sheets(s_WS_DataSheetName).Cells(i, 1).Value = s_WS_row_one

            ' ****************************************************

            ' v_WS_Array takes the substrings from the above address that have been split
            v_WS_Array = Split(Workbooks(s_WS_Filename).Sheets(s_WS_SheetName).Cells(1, i).Address(), "$", -1)

            ' ****************************************************

            ' assign a cell in Data_Sheet the second string in the variant array,
            ' which would be the col letter in the address
            If s_WS_row_one = s_WS_tag_col Then
                s_WS_tag_col_char = v_WS_Array(1)
                s_WS_tag_col_num = Asc(s_WS_tag_col_char) - 64
            End If
            Workbooks(s_WS_Filename).Sheets(s_WS_DataSheetName).Cells(i, 2) = v_WS_Array(1)

            '******************************************************

            ' Using the following worksheet and range
            With Workbooks("OURS.XLSX").Sheets("Sheet1").Range("A1:AH1")

            '******************************************************

            ' Find the range/cell that contains s_WS_row_one
            Set c = .Find(s_WS_row_one)
                If c Is Nothing Then
                    ' If it's nothing, put a dash in the data sheet
                    Workbooks(s_WS_Filename).Sheets(s_WS_DataSheetName).Cells(i, 3).Value = "-"
                Else
                    ' if it does exist, split the OURS address and assign it to a variant array
                    OURS_row_one_a = Split(.Find(s_WS_row_one).Address(), "$", -1)
                    ' assign the value in the data sheet the OURS col letter
                    Workbooks(s_WS_Filename).Sheets(s_WS_DataSheetName).Cells(i, 3).Value = OURS_row_one_a(1)
                End If

            End With

            '********************************************************
    Next

    For i = 2 To i_WS_rowCount ' Going from the top row down to the last row

        '********************************************************

        ' Select the range on the specified sheet
        Application.Goto Workbooks(s_WS_Filename).Sheets(s_WS_SheetName).Range(s_WS_tag_col_char & "1:" & s_WS_tag_col_char & CStr(i_WS_rowCount))

        '********************************************************

        ' put the tag number in col 1, variable row, in s_WS_Tag
        s_WS_Tag = Workbooks(s_WS_Filename).Sheets(s_WS_SheetName).Cells(i, CInt(s_WS_tag_col_num)).Value

        '********************************************************
        'assign a cell in the data sheet the tag stored above
        Workbooks(s_WS_Filename).Sheets(s_WS_DataSheetName).Cells(i, 5).Value = s_WS_Tag

        '********************************************************
        ' With the following workbook/sheet/range
        With Workbooks("OURS.XLSX").Sheets("Sheet1").Range("E1:E500")


                ' Find the range or cell that contains the tag
               Set c = .Find(s_WS_Tag)
        End With

        '********************************************************

        If c Is Nothing Then    ' If the tag doesnt exist
            ' assign val to data sheet saying it doesnt exist
            Workbooks(s_WS_Filename).Sheets(s_WS_DataSheetName).Cells(i, 4).Value = "N/A"
        Else
            ' Else, assign the value to the data sheet saying it has been found
            Workbooks(s_WS_Filename).Sheets(s_WS_DataSheetName).Cells(i, 4).Value = "Located"
            ' assign the address of the tags cell to s_ours_address
            s_ours_address = c.Address()
            ' use the OURS variant again to split the address and parse the row number
            OURS_row_a = Split(s_ours_address, "$", 3)
            ' parse the row number into OURS_row
            s_OURS_row = OURS_row_a(2)
            ' put the row number in the data sheet
            Workbooks(s_WS_Filename).Sheets(s_WS_DataSheetName).Cells(i, 6).Value = s_OURS_row

        End If

        '********************************************************
    Next

    For i = 2 To i_WS_rowCount ' going from the top row to the bottom row

        '********************************************************

        s_WS_row = i     ' Starting with the first row in the sheet being compared (wendy)
        testchar = Workbooks(s_WS_Filename).Sheets(s_WS_DataSheetName).Cells(i, 6).Value

        '********************************************************

        If testchar <> "" Then

            s_OURS_row = Workbooks(s_WS_Filename).Sheets(s_WS_DataSheetName).Cells(i, 6).Value      ' Taking the OURS row num from the data sheet
            ' In Final_Sheet, first column, variable row, placing tag number from data sheet
            Workbooks(s_WS_Filename).Sheets("Final_Sheet").Range("A" & i).Value = Workbooks(s_WS_Filename).Sheets(s_WS_DataSheetName).Range("E" & i)

            '********************************************************

            For j = 2 To i_WS_colCount ' Going from 2d column to last col

                s_WS_col = Workbooks(s_WS_Filename).Sheets(s_WS_DataSheetName).Cells(j, 2).Value ' the compared sheets col is stored in the first col in the data sheet
                s_OURS_col = Workbooks(s_WS_Filename).Sheets(s_WS_DataSheetName).Cells(j, 3).Value  ' the OUR sheet col is stored in the second col in the data sheet

                '********************************************************

                If s_OURS_col <> "-" Then   ' If there isn't a dash marking where there isnt a col letter
                    ' Store the values in the cell marked by the corresponding col and row letters and numbers

                    s_WS_string = Workbooks(s_WS_Filename).Sheets(s_WS_SheetName).Range(s_WS_col & s_WS_row).Value

                    OURS_string = Workbooks("OURS.xlsx").Sheets("Sheet1").Range(s_OURS_col & s_OURS_row).Value

                    If s_WS_string = "" And OURS_string = Empty Then
                        OURS_string = ""
                    End If


                End If

                '********************************************************

                Test_Compare = StrComp(s_WS_string, OURS_string) ' compare the strings

                '********************************************************

                If Test_Compare <> 0 Then   ' store the strings if they dont match. concat them and format to make em look pretty.
                    concat_string = "[" & s_WS_string & "] [" & OURS_string & "]"
                    Workbooks(s_WS_Filename).Sheets("Final_Sheet").Range(s_WS_col & s_WS_row).Value = concat_string ' output them to final_sheet in specified cells.
                End If

                '********************************************************
            Next
        End If
    Next

Workbooks(s_WS_Filename).Sheets("Final_Sheet").Activate





End Sub