Create and loop a column which is based on the difference between a column and a cell

Question

I need to create a column with the difference between a column and a cell (A3) in a loop.

In the picture I would for example like to know impact 1 with the H3 to a H.. = scenario(F3 to F...) - A3 and impact 2= Scenario2(G3...G)-A3 for x years (B3) for example.

I started with an if loop but I struggled to loop the whole column.

Sub Lab1()

    Dim i As Integer
    If i <= Range("B3").Value Then
    
        Range("H3").Value = Range("F3").Value - Range("A3").Value
    
        Range("J3").Value = Range("G3").Value - Range("A3").Value
    
    End If
    i = 2020 + Range("B5").Value
End Sub

Answer 1

I'm a little iffy on where column P from your code comes into play with your screenshot, but this should roughly do what you're looking for I think. Let us know if you run into any issues!

Sub loop1()

'define variables to work with
Dim ws As Worksheet
Dim interCol As Long, scen1Col As Long, impact1Col As Long
Dim firstRow As Long, lastRow As Long
Dim rng As Range
Dim intervention As Long, scenario As Long
Dim i As Long

'define current worksheet
Set ws = ActiveSheet

'define column numbers
interCol = 1 'A
scen1Col = 6 'F
impact1Col = 8 'H

'define start row
firstRow = 3

'end row is the last non-blank cell in Scenario 1 column
lastRow = ws.Cells(ws.Rows.Count, scen1Col).End(xlUp).Row

'loop from first row to last row
For i = firstRow To lastRow
    
    'define cell to update
    Set rng = ws.Cells(i, impact1Col)
        
    'intervention doesn't change from row to row
    intervention = ws.Cells(firstRow, interCol)
    
    'scenario varies from row to row
    scenario = ws.Cells(i, scen1Col)
    
    'update target cell with calculation
    rng = scenario - intervention
    
Next i

End Sub