How to exit a loop once the value is found in the range

Hi Guys,

The following vba code runs when the user clicks the command button. It first calculates the CurrentWk and then searches the range c for the resulting value. I want the macro to run through each cell in the range c and exit the loop once c.value = CurrentWk.

Can anyone assist?

Private Sub cmdForecast_Click()

Dim CurrentWk As Integer
Dim Age As Integer
Dim r As Integer
Dim c As Range

'Go to 12-Week Forecast sheet
Sheets("12-Week Forecast").Select

ActiveSheet.Range("G3").Select

Age = 13

'Calculate Current Week
CurrentWk = ActiveSheet.Range("G3").Value - Age

'Get Final Row
r = Cells(Rows.Count, 1).End(xlUp).Row


Do
For Each c In Range("A5:A" & r)

    If c.Value = CurrentWk Then
        'Select range going down 15 rows
        c.Select
    End If

Next c
Loop Until c.Value = CurrentWk


End Sub

Simplify

Do
For Each c In Range("A5:A" & r)

    If c.Value = CurrentWk Then
        'Select range going down 15 rows
        c.Select
    End If

Next c
Loop Until c.Value = CurrentWk

to

For Each c In Range("A5:A" & r)

    If c.Value = CurrentWk Then
        'Select range going down 15 rows
        c.Select
        Exit For
    End If

Next c

Originally Posted by Andrew-R

Or do it properly, without the need to select random stuff...

Dim FindWk

Set FindWk=Range("A5:A" & r).Find(CurrentWk, lookin:=xlValues, lookat:=xlWhole)

If Not FindWk Is Nothing Then
  FindWk.Select
End If

Avoids the need to loop altogether.

And a lovely solution 'tis!

Liking Andrew's solution better, I would do

Dim FindWk

Set FindWk=Range("A5:A" & r).Find(CurrentWk, lookin:=xlValues, lookat:=xlWhole)

If Not FindWk Is Nothing Then
  Range(FindWk.Offset(0, 0), FindWk.Offset(14, 0)).Select
End If

You can the offset parameters accordingly.