.select efficiency

[Guest]

Selecting cells in a loop routine appears to be terribly inefficient. The code I have is:
Sub errorchk()
Dim s1, s2 As String
Dim r As Variant
Application.ScreenUpdating = False
s1 = "sheet1"
s2 = "sheet2"
For r = 1 To 100
For c = 1 To 26
Cells(r, c).Select
If Sheets(s1).Cells(r, c).Value <> Sheets(s2).Cells(r, c).Value Then MsgBox "error at " & ActiveCell.Address
Next c
Next r
MsgBox "Check complete"
Application.ScreenUpdating = True
Range("a1").Select
End Sub

... but would be grateful if anyone has code that can do the same quicker as the check range needs to expand.

Thank you in anticipation.

 

[Frank Kabel]

Hi
If I read you code correctly just remove the line
Cells(r, c).Select
your code should work fine without this line as you are referencing the
cells directly

Frank

 

[Guest]

Afraid not Frank, as the msgbox relies on the cell being selected to accurately report the location of a variation in values.
I need a way of identifying the particular cells that are being checked in the routine when an error is found.

----- Frank Kabel wrote: -----

Hi
If I read you code correctly just remove the line
Cells(r, c).Select
your code should work fine without this line as you are referencing the
cells directly

Frank

 

[Guest]

Thanks Frank, you put me on the right lines
Sub errorchk(
Dim s1, s2 As Strin
Dim r As Varian
Application.ScreenUpdating = Fals
s1 = "sheet1
s2 = "sheet2
For r = 1 To 10
For c = 1 To 2
'Cells(r, c).Selec
If Sheets(s1).Cells(r, c).Value <> Sheets(s2).Cells(r, c).Value Then MsgBox "error at " & Cells(r, c).Addres
Next
Next
MsgBox "Check complete
Application.ScreenUpdating = Tru
Range("a1").Selec
End Sub

 

[Frank Kabel]

Hi
sorry missed that line. Change
Then MsgBox "error at " & ActiveCell.Address
to
Then MsgBox "error at " & Sheets(s1).Cells(r, c).Address

Frank