Attribute VB_Name = "V03_Cells"
Option Explicit

' Range and Cells: two ways to reach the same cells
Sub Range_And_Cells()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Practice")
    Dim i As Long

    ws.Range("B2").Value = "Written with Range"            ' A1 notation, in quotes
    ws.Cells(3, 2).Value = "Written with Cells"            ' row 3, column 2 = B3

    For i = 1 To 5
        ws.Range("D" & i + 1).Value = i                     ' Range with a variable row
        ws.Cells(i + 1, 5).Value = i * 10                   ' Cells with a variable row
    Next i

    ws.Range(ws.Cells(2, 7), ws.Cells(6, 7)).Value = "Block"   ' a range built from two Cells

    Dim increase As Double
    increase = ThisWorkbook.Worksheets("Inputs").Range("B3").Value   ' read into a variable
    ws.Range("B8").Value = increase                                  ' write it back somewhere else
End Sub

' Cell by cell: clear, then loop down to the last row with data
Sub Apply_Price_Increase()
    Dim wsIn As Worksheet, wsPL As Worksheet
    Set wsIn = ThisWorkbook.Worksheets("Inputs")
    Set wsPL = ThisWorkbook.Worksheets("Price List")

    Dim increase As Double, discount As Double
    increase = wsIn.Range("B3").Value
    discount = wsIn.Range("B4").Value

    Dim lastRow As Long, r As Long, newPrice As Double
    lastRow = wsPL.Cells(wsPL.Rows.Count, 1).End(xlUp).Row
    wsPL.Range("E2:F" & wsPL.Rows.Count).ClearContents

    For r = 2 To lastRow
        newPrice = Application.WorksheetFunction.Round(wsPL.Cells(r, 4).Value * (1 + increase), 2)
        wsPL.Cells(r, 5).Value = newPrice
        wsPL.Cells(r, 6).Value = Application.WorksheetFunction.Round(newPrice * (1 - discount), 2)
    Next r
End Sub

' The fast way: read the whole column into an array, calculate in memory, write back once
Sub Apply_Price_Increase_Array()
    Dim wsIn As Worksheet, wsPL As Worksheet
    Set wsIn = ThisWorkbook.Worksheets("Inputs")
    Set wsPL = ThisWorkbook.Worksheets("Price List")

    Dim increase As Double, discount As Double
    increase = wsIn.Range("B3").Value
    discount = wsIn.Range("B4").Value

    Dim lastRow As Long, n As Long, i As Long
    lastRow = wsPL.Cells(wsPL.Rows.Count, 1).End(xlUp).Row
    n = lastRow - 1

    Dim prices As Variant, results() As Double
    prices = wsPL.Range("D2:D" & lastRow).Value          ' a 2-D array: prices(1 To n, 1 To 1)
    ReDim results(1 To n, 1 To 2)

    For i = 1 To n
        results(i, 1) = Application.WorksheetFunction.Round(prices(i, 1) * (1 + increase), 2)
        results(i, 2) = Application.WorksheetFunction.Round(results(i, 1) * (1 - discount), 2)
    Next i

    wsPL.Range("E2:F" & wsPL.Rows.Count).ClearContents
    wsPL.Range("E2").Resize(n, 2).Value = results         ' one write for the whole block
End Sub

Sub Clear_Results()
    With ThisWorkbook.Worksheets("Price List")
        .Range("E2:F" & .Rows.Count).ClearContents
    End With
End Sub

' VBA Round versus the worksheet ROUND function
Sub Rounding_Demo()
    Dim ws As Worksheet, r As Long
    Set ws = ThisWorkbook.Worksheets("Rounding")
    For r = 2 To 6
        ws.Cells(r, 4).Value = Round(ws.Cells(r, 1).Value, ws.Cells(r, 2).Value)
        ws.Cells(r, 5).Value = Application.WorksheetFunction.Round(ws.Cells(r, 1).Value, ws.Cells(r, 2).Value)
    Next r
End Sub