Attribute VB_Name = "V05_Loops"
Option Explicit

' For...Next: a known number of repetitions, here one per row of the list
Sub Flag_High_Prices()
    Dim ws As Worksheet, r As Long, lastRow As Long, threshold As Double
    Set ws = ThisWorkbook.Worksheets("Price List")
    threshold = ThisWorkbook.Worksheets("Inputs").Range("B3").Value
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ws.Range("E2:E" & ws.Rows.Count).ClearContents

    For r = 2 To lastRow
        If ws.Cells(r, 4).Value > threshold Then ws.Cells(r, 5).Value = "High"
    Next r
End Sub

' Step: every second row, or whatever the lever on sheet Inputs says
Sub Sample_Every_Nth_Row()
    Dim ws As Worksheet, r As Long, lastRow As Long, stepSize As Long
    Set ws = ThisWorkbook.Worksheets("Price List")
    stepSize = ThisWorkbook.Worksheets("Inputs").Range("B4").Value
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ws.Range("G2:G" & ws.Rows.Count).ClearContents

    For r = 2 To lastRow Step stepSize
        ws.Cells(r, 7).Value = "Sample"
    Next r
End Sub

' Do While: repeat while the condition is true. Here: until the first empty cell in column A
Sub Number_Rows_Do_While()
    Dim ws As Worksheet, r As Long
    Set ws = ThisWorkbook.Worksheets("Price List")
    ws.Range("F2:F" & ws.Rows.Count).ClearContents

    r = 2
    Do While ws.Cells(r, 1).Value <> ""
        ws.Cells(r, 6).Value = r - 1
        r = r + 1
    Loop
End Sub

' Do Until: repeat until the condition becomes true. First month in which cumulative net sales reach the target
Sub Payback_Month()
    Dim ws As Worksheet, target As Double, cumulative As Double, m As Long
    Set ws = ThisWorkbook.Worksheets("Monthly Sales")
    target = ThisWorkbook.Worksheets("Inputs").Range("B5").Value

    m = 0
    cumulative = 0
    Do Until cumulative >= target
        m = m + 1
        If ws.Cells(m + 1, 1).Value = "" Then Exit Do      ' ran out of data: leave the loop
        cumulative = cumulative + ws.Cells(m + 1, 2).Value
    Loop

    If cumulative >= target Then
        ThisWorkbook.Worksheets("Inputs").Range("B9").Value = m
    Else
        ThisWorkbook.Worksheets("Inputs").Range("B9").Value = "Not reached"
    End If
End Sub

' Exit For: stop as soon as the answer is found
Sub Find_First_Row()
    Dim ws As Worksheet, r As Long, lastRow As Long, found As Long, product As String
    Set ws = ThisWorkbook.Worksheets("Price List")
    product = ThisWorkbook.Worksheets("Inputs").Range("B6").Value
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    For r = 2 To lastRow
        If ws.Cells(r, 1).Value = product Then
            found = r
            Exit For
        End If
    Next r

    If found > 0 Then
        ThisWorkbook.Worksheets("Inputs").Range("B10").Value = found
    Else
        ThisWorkbook.Worksheets("Inputs").Range("B10").Value = "Not found"
    End If
End Sub

' For Each: one pass over every cell of a range, no row counter needed
Sub Count_High_Prices()
    Dim ws As Worksheet, cel As Range, n As Long, lastRow As Long, threshold As Double
    Set ws = ThisWorkbook.Worksheets("Price List")
    threshold = ThisWorkbook.Worksheets("Inputs").Range("B3").Value
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    For Each cel In ws.Range("D2:D" & lastRow)
        If cel.Value > threshold Then n = n + 1
    Next cel

    ThisWorkbook.Worksheets("Inputs").Range("B11").Value = n
End Sub

' A loop inside a loop: every product in every market
Sub Fill_Matrix()
    Dim ws As Worksheet, r As Long, c As Long
    Set ws = ThisWorkbook.Worksheets("Matrix")
    ws.Range("C3:F8").ClearContents

    For r = 3 To 8                                   ' products, down the rows
        For c = 3 To 6                               ' markets, across the columns
            ws.Cells(r, c).Value = Application.WorksheetFunction.Round(ws.Cells(r, 2).Value * ws.Cells(2, c).Value, 2)
        Next c
    Next r
End Sub

' Backwards: delete rows safely. Each deletion moves the rows below up, so start at the bottom
Sub Delete_Discontinued()
    Dim ws As Worksheet, r As Long, lastRow As Long
    Set ws = ThisWorkbook.Worksheets("Delist")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    For r = lastRow To 2 Step -1
        If ws.Cells(r, 2).Value = "Discontinued" Then ws.Rows(r).Delete
    Next r
End Sub

' The same job counting forwards: it skips the row that moves up into the gap
Sub Delete_Discontinued_Wrong()
    Dim ws As Worksheet, r As Long, lastRow As Long
    Set ws = ThisWorkbook.Worksheets("Delist")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    For r = 2 To lastRow
        If ws.Cells(r, 2).Value = "Discontinued" Then ws.Rows(r).Delete
    Next r
End Sub

' Put the list back as it was, with a direct assignment of values
Sub Reset_Delist()
    With ThisWorkbook.Worksheets("Delist")
        .Range("A2:B" & .Rows.Count).ClearContents
        .Range("A2:B13").Value = ThisWorkbook.Worksheets("Delist Backup").Range("A2:B13").Value
    End With
End Sub

Sub Clear_Loop_Results()
    ThisWorkbook.Worksheets("Price List").Range("E2:G" & ThisWorkbook.Worksheets("Price List").Rows.Count).ClearContents
    ThisWorkbook.Worksheets("Matrix").Range("C3:F8").ClearContents
    ThisWorkbook.Worksheets("Inputs").Range("B9:B11").ClearContents
End Sub