Looping code

[Rob]

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.

 

[Subodh]

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

 

[OssieMac]

Hi Rob,

Not really sure that I understand the question correctly.
I interpretted as the loop to be after the form is closed. Is this correct?
When you say empty row, do you mean the entire row because that is how I
interpreted?

CountA counts the number of cells that are NOT empty in the row therefore if
it returns zero then the row is empty.


Sub CheckVolumeRise()
Dim lngCol As Long
Dim lngRow As Long

If ActiveCell < ActiveCell.Offset(0, 2) And _
ActiveCell.Offset(0, 2) < ActiveCell.Offset(0, 4) Then

Selection.End(xlToLeft).Select

CriteriaReached.Show

'following code will not run until
'after the form is closed.
Selection.End(xlToRight).Select
ActiveCell.Offset(1, -4).Activate

lngCol = ActiveCell.Column

For lngRow = ActiveCell.Row To Rows.Count
If WorksheetFunction.CountA _
(Cells(lngRow, lngCol).EntireRow) = 0 Then

MsgBox "Row " & lngRow & " Is empty."
Exit For 'Don't go any further
End If
Next lngRow
Else
ActiveCell.Offset(1, 0).Activate
End If
End Sub

 

[OssieMac]

I have found that UsedRange is a very unreliable method of finding the last
row. If the cells below the last data cells are formatted then usedrange can
include them even though they have no data. Also, if you enter data in any
cells below the normal data range and then clear those cells then usedrange
can include the cleared cells.