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

DSlomer64 · Accepted Answer · 2023-11-23 01:10:11Z

3

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

edited Nov 23, 2023 at 1:10

DSlomer64

4,2934 gold badges57 silver badges92 bronze badges

answered Aug 9, 2017 at 17:45

Shai Rado

33.7k7 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ron Rosenfeld · Accepted Answer · 2017-08-09 18:11:01Z

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

edited Aug 9, 2017 at 18:11

answered Aug 9, 2017 at 18:03

Ron Rosenfeld

62.1k7 gold badges35 silver badges72 bronze badges

Comments

ale10ander · Accepted Answer · 2017-08-09 18:56:48Z

1

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

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.

edited Aug 9, 2017 at 18:56

answered Aug 9, 2017 at 17:44

ale10ander

1,0065 gold badges23 silver badges44 bronze badges

1 Comment

Tox Over a year ago

Yes I understand that, but since it will eventually be used in a larger tool that I am building I want everything to be calculated by vba.

Collectives™ on Stack Overflow

Excel VBA offset property (NOT function)

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related