.select efficiency
 

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.

.select efficiency
 

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

Lawlera wrote:
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.

.select efficiency
 

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

Lawlera wrote:
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.

.select efficiency
 

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

.select efficiency
 

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

Frank

Lawlera wrote:
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

Lawlera wrote:
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.