Help with Relative References

[RegularExcelUser]

Hi there
I need some help with relative references. The code below is a macro I recorded with fixed references, and it works fine e.g. when I position my cursor at a certain point, it will add 9 rows above it, copy data from the 9 rows above, paste it into the new 9 rows, and then modify some formulas and text. So far, so good. When I try recording the same actions in a macro with relative references, it doesn't work e.g. the cell references are all over the shop, it ends up inserting the new rows much further up my sheet, tries to make edits to other unrelated cells, etc. This is irrespective of whether I start in the cell above row 106, or in row 106 (I thought that might be introducing some issue with negative R1s). To that end, I'd rather just fix the code below to put the R1C1 references in, but not sure how to do that e.g. do I need to make some adjustment to the variables "Rows" or "Range" to make the R1C1 work for single cell/multiple cell references. To make this a little easier, pieces in particular that I'm interested in understanding how to amend are Rows("106:114").Select, Range("B105").Select and Range("I97:J97").Select. If I can wrap my head around these 3, I can figure out the rest myself.

Sub TEST_OF_ADDING_MULTIPLE_ROWS()
'
' TEST_OF_ADDING_MULTIPLE_ROWS Macro
'

'
    Rows("106:114").Select
    Selection.Insert Shift:=xlDown
    Range("B105").Select
    Selection.Copy
    Range("B106").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("A106").Select
    ActiveCell.FormulaR1C1 = "'Period"
    Range("B106").Select
    Selection.Copy
    Range("B107").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("A107").Select
    ActiveCell.FormulaR1C1 = "'Amount"
    Range("B107").Select
    Selection.Copy
    Range("B108").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("A108").Select
    ActiveCell.FormulaR1C1 = "'Amount"
    Range("A108").Select
    Selection.InsertIndent 1
    Range("B108").Select
    Selection.Copy
    Range("B109").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("A109").Select
    ActiveCell.FormulaR1C1 = "'Contract Penalty Amount"
    Range("B109").Select
    Selection.Copy
    Range("B110").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("A110").Select
    ActiveCell.FormulaR1C1 = "'Contract Penalty Amount"
    Range("A110").Select
    Selection.InsertIndent 1
    Range("B110").Select
    Selection.Copy
    Range("B111").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("A111").Select
    ActiveCell.FormulaR1C1 = "'-14 Days"
    Range("B111").Select
    Selection.Copy
    Range("B112").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("A112").Select
    ActiveCell.FormulaR1C1 = "'- 7 Days"
    Range("B112").Select
    Selection.Copy
    Range("B113").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("A113").Select
    ActiveCell.FormulaR1C1 = "'-1 Day"
    Range("B113").Select
    Selection.Copy
    Range("B114").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("A114").Select
    ActiveCell.FormulaR1C1 = "'Add Period"
    Range("H97").Select
    Selection.Copy
    Range("H106").Select
    ActiveSheet.Paste
    [SIZE=6][COLOR=rgb(44, 130, 201)]Range("I97:J97")[/COLOR][/SIZE].Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("I106").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("H98:H104").Select
    Selection.Copy
    Range("H107").Select
    ActiveSheet.Paste
    Range("I106").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Enter Start Period For Next Amount Here"
    Range("J106").Select
    ActiveCell.FormulaR1C1 = "Enter End Period for 1st Amount here"
    Range("J106").Select
    ActiveCell.FormulaR1C1 = "Enter End Period for Next Amount here"
    Range("H106").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[1]=""Enter Start Period For Next Amount Here"","""",IF(AND(RC[1]<>""Enter Start Period For Next Amount Here"",RC[2]=""Enter End Period for Next Amount here""),RC[1],IF(AND(RC[1]<>""Enter Start Period For Next Amount Here"",RC[2]<>""Enter End Period for Next Amount here""),RC[1]&"" - ""&RC[2])))"
    Range("H111").Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Range("A114").Select
End Sub

 

[Micron]

If you want a cell address to be relative, you must base it on the starting point. If you're invoking that code from the Macro options on the Ribbon, then you could get the address at the start and make use of Offset, provided you select the right cell at the outset. Or you could base the starting point using a variable in cases where you want that to be (e.g.) last row in column A that has data, or the last row in the last column with data on the sheet. TBH I don't use macro recorder much so probably have forgotten that you even have the option to use relative referencing with that.
HTH

 

[Joe4]

To build on what Micron said, you need a "starting point" in order to do relative referencing. It can by dynamic.
For example, "ActiveCell" gives you the active cell (where the cursor is placed) at the time you run the code.
You can then move relative to that using the "Offset" function, like Micron said.

For example, to move nine rows up from the current cell, you would use:

ActiveCell.Offset(-9, 0)

You can also get the row and column numbers of the ActivCell like this:

r = ActiveCell.Row
c = ActiveCell.Column

that is often helpful.

So let's say that you wanted to select the cell 9 rows up from the ActiveCell, but in column B.
One way would be to do it like this:

r = ActiveCell.Row
Cells(r - 9, "B").Select

Note that you can also use 2 in place of "B", as you can either use the letter reference or index reference for your column, i.e.
Cells(r - 9, 2).Select

One last thing. When working with ranges, it usually is not necessary to Select them in order to work with them. Doing so will actually slow your code down, so it is best to avoid, or at least limit the number of "Select" statements you have.
So in the example above, if you ultimately wanted to enter some text in that cells 9 rows up and in column B, you could just do that like this:

r = ActiveCell.Row
Cells(r - 9, "B") = "Enter Start Period For Next Amount Here"

Hope that helps give you some tips on working with your ranges.

 

[RegularExcelUser]

To build on what Micron said, you need a "starting point" in order to do relative referencing. It can by dynamic.
For example, "ActiveCell" gives you the active cell (where the cursor is placed) at the time you run the code.
You can then move relative to that using the "Offset" function, like Micron said.

For example, to move nine rows up from the current cell, you would use:

ActiveCell.Offset(-9, 0)

You can also get the row and column numbers of the ActivCell like this:

r = ActiveCell.Row
c = ActiveCell.Column

that is often helpful.

So let's say that you wanted to select the cell 9 rows up from the ActiveCell, but in column B.
One way would be to do it like this:

r = ActiveCell.Row
Cells(r - 9, "B").Select

Note that you can also use 2 in place of "B", as you can either use the letter reference or index reference for your column, i.e.
Cells(r - 9, 2).Select

One last thing. When working with ranges, it usually is not necessary to Select them in order to work with them. Doing so will actually slow your code down, so it is best to avoid, or at least limit the number of "Select" statements you have.
So in the example above, if you ultimately wanted to enter some text in that cells 9 rows up and in column B, you could just do that like this:

r = ActiveCell.Row
Cells(r - 9, "B") = "Enter Start Period For Next Amount Here"

Hope that helps give you some tips on working with your ranges.

Thanks for the response, Joe4. It didn't answer my question exactly, but did give me some food for thought, which I then applied to building up a new code using the ActiveCell.Offset () and cobbling together pieces of code from several different macros I had run with relative and non-relative references. A lot of trial and error, but I got there in the end, in no small part to you providing the inspiration!

 

[Joe4]

You are welcome.
Glad we were able to help steer you in the right direction!