Basic Looping Question

J

Jenn

I am trying some exercises on how to do loops since I am new at coding. I have practiced with the following one. What I would like to do is instead of saying for rows 1 -15 I want it to be dynamic to perform the code until the end on the values in the column (lets assume someone adds a value in row 16, the code below would not evalute row 16). Thanks in advance...

Sub ExitForDemo()
Dim MaxVal As Double
Dim Row As Long
Dim TheCell As Range

MaxVal = Application.WorksheetFunction.Max(Range("A:A"))
For Row = 1 To 15
Set TheCell = Range("A1").Offset(Row - 1, 0)
If TheCell.Value = MaxVal Then
MsgBox "Max Value is in Row " & Row & " Equals " & TheCell.Value

TheCell.Activate
Exit For
End If
Next Row
End Sub
 
B

Bob Phillips

Sub ExitForDemo()
Dim MaxVal As Double
Dim Row As Long
Dim TheCell As Range

MaxVal = Application.WorksheetFunction.Max(Range("A:A"))
For Row = 1 To Cells(Rows.Count,"A").End(xlUp).Row
Set TheCell = Range("A1").Offset(Row - 1, 0)
If TheCell.Value = MaxVal Then
MsgBox "Max Value is in Row " & Row & " Equals " & TheCell.Value

TheCell.Activate
Exit For
End If
Next Row
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Jenn said:
I am trying some exercises on how to do loops since I am new at coding. I
Click to expand...
have practiced with the following one. What I would like to do is instead of
saying for rows 1 -15 I want it to be dynamic to perform the code until the
end on the values in the column (lets assume someone adds a value in row 16,
the code below would not evalute row 16). Thanks in advance...
 
D

Don Guillett

Would this find your max value a bit quicker
Sub findmax()
MsgBox "max value in row " & _
Application.Match(9.99999999E+307, columns(1))
End Sub

--
Don Guillett
SalesAid Software
[email protected]
Jenn said:
I am trying some exercises on how to do loops since I am new at coding. I
Click to expand...
have practiced with the following one. What I would like to do is instead of
saying for rows 1 -15 I want it to be dynamic to perform the code until the
end on the values in the column (lets assume someone adds a value in row 16,
the code below would not evalute row 16). Thanks in advance...
 
D

Don Guillett

My first posting found the last value in the column.This finds the row of
the highest value

Sub findmax()
MsgBox "max value in row " & _
Columns(1).Find(Application.Max(Columns(1))).Row
End Sub
--
Don Guillett
SalesAid Software
[email protected]
Jenn said:
I am trying some exercises on how to do loops since I am new at coding. I
Click to expand...
have practiced with the following one. What I would like to do is instead of
saying for rows 1 -15 I want it to be dynamic to perform the code until the
end on the values in the column (lets assume someone adds a value in row 16,
the code below would not evalute row 16). Thanks in advance...
 
Top