VBA: using relative references in a for...next loop

[CedricMattelaer]

Just a really easy question, but after a day spent on the internet without finding the solution I thought I might subscribe to this forum and ask it here.

I currently have this line of code:

For i = 1 To 272
For j = 1 To 13

If Worksheets("exercice").Cells(i, 1) = "1" Then
Worksheets("exercice").Cells(344 + i, 3 + j) = "=C288"
Next i
Next j

Problem is, if j goes up with 1, I also want the relative reference to "=C288" to become "=D288".

Is there a way to do this?

I could write, for example, =Cells(288, 3+j) but then only the value of the cell is copied, and I need the relation between both (ie instead of "1" in that cell, I need "=D288")

I hope I explained it ok and thanks for sharing your knowledge!

 

[Scott Huish]

What's the exact range you are working with,

and why not change the formula to include the condition and apply the formula to the whole range at once?

i.e.

=IF(A1=1,C288,"")

 

[Rick Rothstein]

Does this do what you want...

Worksheets("exercice").Cells(344 + i, 3 + j) = "=" & Range("C288").Offset(, j - 1).Address(0, 0)

 

[Rick Rothstein]

Does this do what you want...

Worksheets("exercice").Cells(344 + i, 3 + j) = "=" & Range("C288").Offset(, j - 1).Address(0, 0)

Actually, this might be simpler to understand...

Worksheets("exercice").Cells(344 + i, 3 + j) = "=" & Chr(66 + j) & "288"

 

[CedricMattelaer]

@ Hotpepper, I don't really understand what you mean. Obviously, the "program" is longer than the lines I posted

@Rick:
This is great! I'm still trying to figure out the syntaxis and trying to apply it to the other lines, but this works great for the line I posted! Thanks a lot

 

[mikerickson]

The j loop is unnessesary

With Worksheets("Exercize")
    For i = 1 to 272
        If .Cells(i, 1) = "1" Then
            .Cells(341+i, 4).Resize(1, 13).FormulaR1C1 = "=R288C[-1]"
        End If
    Next i
End With

 

[Scott Huish]

Instead of just putting in =C288, you can eliminate the loop altogether by changing your formula.

If you use relative references in your formula, when you apply it to an entire range, then the references will adjust.

For example

Sub test
Range("A1:A10").Formula = "=B1"
End Sub

Now A2 will not contain =B1, it will contain =B2, etc.

 

[Rick Rothstein]

The j loop is unnessesary

Hmm! It never occurred to me to look at what the loops were actually doing. As it turns out, it looks like no loops are necessary...

With Worksheets("Exercize").Range("D345:D616")
    .Value = "@C288"
    .Replace "@", "=", xlPart
    .Copy .Resize(, 13)
End With

Seems strange, though, that the OP wanted to put the same formulas all the way down each column . In case that is wrong and he want the row references to increase as the formulas went down the columns, we can still do this without any loops...

Worksheets("Exercize").Range("D345").Resize(272, 13).Value = "=C288"

 

[mikerickson]

The OP code has the If Ai = "1" condition that requires a loop (or a filter etc. ....)

 

[Rick Rothstein]

The OP code has the If Ai = "1" condition that requires a loop (or a filter etc. ....)

Ah, I forgot about that. Okay, still no loop (I'm doing the "etc.")...

Dim UnusedCol As Long
With Worksheets("Exercize")
    With .Range("D345:D616")
        .Value = "@C288"
        .Replace "@", "=", xlPart
        .Copy .Resize(, 13)
    End With
    UnusedCol = .Cells.Find(What:="*", SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
    .Range("D345:D616").Offset(-344, -3).Copy .Cells(345, UnusedCol)
    With Columns(UnusedCol)
        .Replace 1, "=1", xlWhole
        On Error Resume Next
        Intersect(Columns("D:P"), .SpecialCells(xlCellTypeFormulas).EntireRow).Clear
        .Clear
    End With
End With