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

CedricMattelaer

New Member
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

MrExcel MVP
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

MrExcel MVP
Does this do what you want...
Code:
``Worksheets("exercice").Cells(344 + i, 3 + j) = "=" & Range("C288").Offset(, j - 1).Address(0, 0)``

Rick Rothstein

MrExcel MVP
Does this do what you want...
Code:
``Worksheets("exercice").Cells(344 + i, 3 + j) = "=" & Range("C288").Offset(, j - 1).Address(0, 0)``
Actually, this might be simpler to understand...
Code:
``Worksheets("exercice").Cells(344 + i, 3 + j) = "=" & Chr(66 + j) & "288"``

CedricMattelaer

New Member
@ 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

MrExcel MVP
The j loop is unnessesary
Code:
``````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

MrExcel MVP
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

MrExcel MVP
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...
Code:
``````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...
Code:
``Worksheets("Exercize").Range("D345").Resize(272, 13).Value = "=C288"``

Last edited:

mikerickson

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

Rick Rothstein

MrExcel MVP
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.")...
Code:
``````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``````

1,081,532
Messages
5,359,359
Members
400,524
Latest member
Excelbat

This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...