Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping code
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 rows until an empty row is reached. Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping code
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 -- Regards, OssieMac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping code
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. -- Regards, OssieMac "Subodh" wrote: 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 rows 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping Code | Excel Programming | |||
looping code | Excel Programming | |||
Looping rather than code for each row | Excel Programming | |||
Help with Looping Code | Excel Programming | |||
Code looping when it should not | Excel Programming |