Relative reference of cells with loop

Question

I'm trying to create a loop that writes a formula referencing a range of cells above it.

Dim y1 As Integer
Dim x1 As Integer
Dim x2 As Integer
Dim y2 As Integer
Dim i

x1 = 5
y1 = 10
x2 = 43


  For i = 1 To 500

  Range(Cells(x2, 4)).Value = "=1-Sum(" & Range(Cells(x1, 4), Cells(y1, 4)) & ")"""
    x1 = x1 + 22
    y1 = y1 + 22
    x2 = x2 + 22
    y2 = y2 + 22

  Next

So for cell D21 I'd like it to say "=1-SUM(D5:D10)", D43 "=1-sum(D27:D32)", etc.

Answer 1

Range needs two cells, a start and an end(or a string).

Range(Cells(x2, 4))

should just be

Cells(x2, 4)

Also

Range(Cells(x1, 4), Cells(y1, 4))

returns an array of values that you are trying to concatanate into a string.

You need to return the Address which a string:

Range(Cells(x1, 4), Cells(y1, 4)).Address(0,0)

Other Notes:

• One should get in the practice of assigning the parent sheet to all range objects
• All the + 22 can be located inline using i as a multiplyer.
• Use Long instead of Integer when referencing row numbers as they can exceed the allowed value of an Integer.

---

Dim y1 As Long
Dim x1 As Long
Dim x2 As Long
Dim i As Long


x1 = 5
y1 = 10
x2 = 43

With Worksheets("Sheet1") 'Change to your sheet
    For i = 1 To 500
      .Cells((i - 1) * 22 + x2, 4).Value = "=1-Sum(" & .Range(.Cells((i - 1) * 22 + x1, 4), .Cells((i - 1) * 22 + y1, 4)).Address(0,0) & ")"
    Next
End With