CODEWITHSUNDEEP
excelvbaloopsreferencecell
Slash
Slash

Reputation: 509

Relative reference of cells with loop

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.

Upvotes: 0

Views: 108

Answers (1)

Scott Craner
Scott Craner

Reputation: 152495

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

Upvotes: 1

Related Questions