Looping code
On Mar 17, 8:51*am, Rob wrote:
Hi,
The code below checks data in a row and depending on the outcome will
display a User Form. If the data in the row does not meet the criteria to
show the User Form then a cell in the row below is activated. If the User
Form is displayed (because the data has met the criteria) then, on closing it
as previously mentioned *a cell in the row below is activated.
Sub CheckVolumeRise()
If ActiveCell <ActiveCell.Offset(0, 2) And *_
ActiveCell.Offset(0, 2) < *ActiveCell.Offset(0, 4) Then
Selection.End(xlToLeft).Select
CriteriaReached.Show
Selection.End(xlToRight).Select
ActiveCell.Offset(1, -4).Activate
Else
ActiveCell.Offset(1, 0).Activate
End If
End Sub
Please will someone show me how to loop this code down through the row’s
until an empty row is reached.
Thank you.
I have modified the code as follows
Try this out.
Sub Chkrise()
Dim nosofrows As Long
start:
If ActiveCell < ActiveCell.Offset(0, 2) And _
ActiveCell.Offset(0, 2) < ActiveCell.Offset(0, 4) Then
Selection.End(xlToLeft).Select
criteriaReached.Show
'Selection.End(xlToRight).Select (not considered necessary so omitted)
'ActiveCell.Offset(1, -4).Activate (not considered necessary so
omitted)
Else
ActiveCell.Offset(1, 0).Activate
nosofrows = ActiveSheet.UsedRange.Rows.Count + _
ActiveSheet.UsedRange.Row - 1 'this gives the last rows that is used
If ActiveCell.Row < nosofrows Then GoTo start
End If
'ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell ).Activate
End Sub
|