PDA

View Full Version : [SOLVED] Comparing cells from different files



Paleo
02-02-2005, 08:27 PM
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
02-02-2005, 08:40 PM
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
02-02-2005, 08:59 PM
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
02-02-2005, 09:16 PM
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
02-02-2005, 09:21 PM
Not a prob,
BTW, you should also be able to use Sheets("CN3") instead of Worksheets("CN3")
John

Paleo
02-02-2005, 09:25 PM
Hi John,

I was trying that just now, :rotlaugh: .

Thanks again.

Jacob Hilderbrand
02-02-2005, 11:30 PM
Not a prob,
BTW, you should also be able to use Sheets("CN3") instead of Worksheets("CN3")
John:fyi:
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. :2p: