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

CedricMattelaer

New Member
Joined
Jun 16, 2011
Messages
37
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
Joined
Mar 17, 2004
Messages
19,880
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
Joined
Apr 18, 2011
Messages
35,498
Office Version
2010
Platform
Windows
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
Joined
Apr 18, 2011
Messages
35,498
Office Version
2010
Platform
Windows
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
Joined
Jun 16, 2011
Messages
37
@ 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
Joined
Jan 15, 2007
Messages
22,775
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
Joined
Mar 17, 2004
Messages
19,880
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
Joined
Apr 18, 2011
Messages
35,498
Office Version
2010
Platform
Windows
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
Joined
Jan 15, 2007
Messages
22,775
The OP code has the If Ai = "1" condition that requires a loop (or a filter etc. ....)
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,498
Office Version
2010
Platform
Windows
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
 

Forum statistics

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

Some videos you may like

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...
Top