Comparing cells from different files

[Paleo]

How can I compare cells from different excel files?

I've tried with this code with no success:

Sub test()
    Dim ULin As Long
    ULin = Range("A65536").End(xlUp).Row
    For i = 2 To ULin
        If Range("AE" & i).Value = Windows("CN.xls").Range("R" & i).Value Then
            Range("AF" & i) = "Ok"
        Else
            Range("AF" & i) = "Problem"
        End If
    Next
End Sub

[Paleo]

I know I could do it this way:

Sub test()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim ULin As Long
ULin = Range("A65536").End(xlUp).Row
For i = 2 To ULin
Range("AF" & i).Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=[CN.xls]Can_nov!R" & (i + 2) & "C18,""Ok"",""Errado"")"
Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

[johnske]

Hi Paleo, try this:

Sub test2()
      Dim ULin As Long, i%
      ULin = Range("A65536").End(xlUp).Row
            For i = 2 To ULin
                  If Range("AE" & i) = Workbooks("CN.xls").Worksheets(1).Range("R" & i) Then
                        Range("AF" & i) = "Ok"
                  Else
                        Range("AF" & i) = "Problem"
                  End If
            Next
End Sub

[Paleo]

Hi John,

worked just fine. I made only one change. From "Worksheets(1)" to "Worksheets("CN3")", because I have more than one sheet there, but you couldnt know that.

One more thing. The workbook CN.xls must be openned in order to this work.

Thank you very much!

[johnske]

Not a prob,
BTW, you should also be able to use Sheets("CN3") instead of Worksheets("CN3")
John

[Paleo]

Hi John,

I was trying that just now, .

Thanks again.

[Jacob Hilderbrand]

Not a prob,
BTW, you should also be able to use Sheets("CN3") instead of Worksheets("CN3")
John

That is true, usually you can use both interchangably. Sheets are any type of sheet in a workbook, whereas Worksheets are only worksheet type sheets.

To make that a bit clearer, chart sheets are not Worksheets, but they are Sheets. So if you had chart sheets and worksheets in a workbook you could use Worksheets(1) to get the first worksheet and ignore any chart sheets that may be before it.