Excel VBA offset property (NOT function)

Question

I have an Excel file with information in column A and column B. Since these columns could vary in the number of rows I would like to use the function offset so that I could print the formula in one time as an array rather than looping over the formula per cell (the dataset contains almost 1 million datapoints).

My code is actually working the way I want it to be I only can't figure out how to print the code in Range(D1:D5). The outcome is now printed in Range(D1:H1). Anybody familiar how to use this offset within a for statement?

Sub checkOffset()

Dim example As Range
Dim sht As Worksheet
Dim LastRow As Long

Set sht = ThisWorkbook.Worksheets("Sheet1")
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

Set example = Range("A1:A1")

For i = 1 To LastRow
    example.Offset(0, i + 2).Formula = "=SUM(A" & i & ":B" & i & ")"
Next i

End Sub

Answer 1

Using the Offset(Row, Column) PROPERTY, you want to offset with the increment of row (i -1), and 3 columns to the right (from column "A" to column "D")

Try the modified code below:

Set example = Range("A1")

For i = 1 To LastRow
    example.Offset(i - 1, 3).Formula = "=SUM(A" & i & ":B" & i & ")"
Next i

Answer 2

1. You don't need to use VBA for this. Simply type =sum(A1:B1) in cell D1 and then fill it down.

2. If you're going to use VBA anyway, use this:

   Sub checkOffset()
   Dim example As Range
   Dim sht As Worksheet
   Dim LastRow As Long
   
   Set sht = ThisWorkbook.Worksheets("Sheet1")
   LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
   
   Set example = Range("A1:A1")
   
   For i = 1 To LastRow
       example.Offset(i - 1, 3).Formula = "=SUM(A" & i & ":B" & i & ")"
   Next i
   
   End Sub
   

The way offset works is with row offset, column offset. You want the column to always be fixed at 3 to the right.

Answer 3

One way of outputting the formula in one step, without looping, to the entire range, is to use the R1C1 notation:

Edit: Code modified to properly qualify worksheet references

Option Explicit
Sub checkOffset()

Dim example As Range
Dim sht As Worksheet
Dim LastRow As Long

Set sht = ThisWorkbook.Worksheets("Sheet1")

With sht
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set example = .Range(.Cells(1, 1), .Cells(LastRow, 1))
End With

example.Offset(columnoffset:=3).FormulaR1C1 = "=sum(rc[-3],rc[-2])"

End Sub