Compare two separate documents VBA

So what I want to do is have a macro in Workbook A that opens Workbook B and C. After that it goes through column A of Workbooks B and C and where the two values are equal it takes the value from workbook C and pastes it into column A of workbook A.

I have written the below code, however it you think it's easier to do it another way please feel free to write your own one. Thank you and please help me :)

Sub ReportCompareAlta()
'
' ReportCompareAlta Macro
' Adds instances where column D is "ALTA"

    Dim varSheetA As Variant
    Dim varSheetB As Variant
    Dim varSheetC As Variant
    Dim StrValue As Variant
    Dim strRangeToCheck As String
    Dim iRow As Long
    Dim iCol As Long
    Dim WbkA As Workbook
    Dim WbkB As Workbook
    Dim WbkC As Workbook
    Dim counter As Long

    Set WbkA = Workbooks.Open(Filename:="G:\Reporting\AH_MISSE_FEB2013.xls")
    Set WbkB = Workbooks.Open(Filename:="G:\Reporting\AH_MISSE_MAR2013.xls")
    Set WbkC = Workbooks.Open(Filename:="G:\Reporting\ReportCompare.xls")
    Set varSheetA = WbkA.Worksheets("LocalesMallContratos")
    Set varSheetB = WbkB.Worksheets("LocalesMallContratos")
    Set varSheetC = WbkC.Worksheets("Sheet1")


    strRangeToCheck = "A1:IV65536"

    Debug.Print Now
    varSheetA = WbkC.Worksheets("Sheet2").Range(strRangeToCheck) 'may be confusing code here
    varSheetB = WbkC.Worksheets("Sheet3").Range(strRangeToCheck) 'may be confusing code here
    Debug.Print Now

    counter = 0

    For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)        
            If varSheetB(iRow, "B") = varSheetA(iRow, "B") & varSheetB(iRow, "B") <> "GERENCIA" & varSheetB(iRow, "B").Value <> "" & varSheetB(iRow, "D") = "ALTA" Then
                StrValue = ""
                varSheetB.Range("iRow:B").Select 
                Selection = StrValue
                ActiveSheet = varSheetC
                Range("A1").Select
                Selection.Offset(counter, 0).Value = StrValue
                counter = counter - 1

            Else
                MsgBox ("Done")
            End If         
    Next iRow

End Sub

Answers


I see some obvious errors as Mat Richardson points out, using the & is not equivalent shorthand for the AND operator, it's a concatenator, which is not likely what you want when you say:

If varSheetB(iRow, "B") = varSheetA(iRow, "B") & varSheetB(iRow, "B") <> "GERENCIA" & varSheetB(iRow, "B").Value <> "" & varSheetB(iRow, "D") = "ALTA" Then

Which brings me to another error:

varSheetB (and A, and C for that matter) are Variant/Array variables. You cannot index these by iRow, "B" because you cannot use a non-numeric index. Perhaps you mean (iRow, 2).

On a related note: varSheetB.Range("iRow:B").Select this will also fail, because you cannot .Select a Variant. This is not a Range variable. Further, iRow:B is not correct for either of a Variant array or a Range variable. Also, at this point, varSheetB is no longer a Worksheet object variable.

Which brings me to probably the biggest error: You are using the variables varSheetA, varSheetB, and varSheetC to represent (at different times in this code) both a Worksheet Object and a Variant array of values. This is confusing, and probably causing you the errors described above. A variable cannot be both of these things at the same time, so you need to be treating your variants like variants when they are variants, and like worksheets when they are worksheets, or better yet: use worksheet variables for worksheets and variants for arrays, don't use the same variable for multiple purposes.

Sub ReportCompareAlta()
'
' ReportCompareAlta Macro

Dim varSheetA As Worksheet
Dim varSheetB As Worksheet
Dim varSheetC As Worksheet
Dim RangeToCheck As Range
Dim cl as Range
Dim iRow As Long
Dim iCol As Long
Dim WbkA As Workbook
Dim WbkB As Workbook
Dim WbkC As Workbook
Dim counter As Long

Set WbkA = Workbooks.Open(Filename:="G:\Reporting\AH_MISSE_FEB2013.xls")
Set WbkB = Workbooks.Open(Filename:="G:\Reporting\AH_MISSE_MAR2013.xls")
Set WbkC = Workbooks.Open(Filename:="G:\Reporting\ReportCompare.xls")
Set varSheetA = WbkA.Worksheets("LocalesMallContratos")
Set varSheetB = WbkB.Worksheets("LocalesMallContratos")
Set varSheetC = WbkC.Worksheets("Sheet1")


Set RangeToCheck = varSheetA.Range("A1:A65536") '## I change this because you only indicate you want to compare column A ##'

counter = 0

'## just loop over the cells in the range. ##'
'## This is not the most efficient, but it is the easiest ##'
For each cl in RangeToCheck  

    '## Do your comparison here, e.g: ##'
    '## Ignore cells where .Offset(0,3).Value = "ALTA" Or cl.Value = "" ##'
    If not cl.Offset(0,3).Value = "ALTA" Or Not cl.Value = vbNullString Then
        If Not cl.Value = varSheetB.Range(cl.Address).Value Then
           '## The values are not equal, so do something:
                varSheetC.Range(cl.Address) = "not equal"
           counter = counter+1
        Else:
           '## The values are equal, so do something else:
            varSheetC.Range(cl.Address) = "equal"
        End If
   End If
Next


MsgBox "Done! There were " & counter & " mismatch values", vbInformation
End Sub

You need to use the word 'AND' instead of the '&' symbol in your code. Using '&' just concatenates the values, which will cause your if statement to fail.


Need Your Help

ExtJS 4 How to cancel all pending edits in a Grid with Cell Editing and Store data from remote source?

extjs grid edit

I have tried looking for a cancel or reject changes in the grid and the store, but I can seem to find anything. Can't find anything in the forums either.