Change from absolute to relative reference in Excel formula (VBA)

1
votes

Unfortunately I couldn’t find the answer to the below in the other questions – my problem is related to copying and pasting a formula that would use relative cell references instead of dynamic ones.

The general problem with the workbook I’m working on is the fact that it contains of a couple of different sheets with a potential dynamic range change. To give a better outline:

  • The column the formula has to be in, is based on an offset cell – I cannot give it a static value – and starts in row 2;
  • The formula itself is as follows: =CONCATENATE(LEFT(AA2,13), “:”, RIGHT(AA2,5) (and the values to be concatenated will always appear in the AA column)

The macro is supposed to insert the formula in the offset cell, copy it and paste it in the entire column, so the cell in row 3 refers to cell AA3 and so on:

Sub Copy1()

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.End(xlToRight).Select
ActiveCell.Offset(, 1).Select

Dim rng as Range
Set rng = ActiveCell
rng.Select

rng.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(LEFT(R2C27,13), "":"", RIGHT(R2C27,5))"
rng.Offset(1, 0).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.PasteSpecial
Selection.EntireColumn.Select
Application.CutCopyMode = False

End Sub

The problem is – upon trying to insert exact name of the cell, the macro populates it with the following: 

=CONCATENATE(LEFT(‘AA2’,13), "":"", RIGHT(‘AA2’,5))

Because of the quotation marks, the formula doesn’t work.

Using the reference R2C27 results with absolute values being copied and thus every single cell in the column refers to cell AA2.

Is there any possibility make it create, copy and paste relative reference instead of absolute?

2
vbaexcel
easier to change to R1C1 reference style in the Excel Formula Options to copy the formula from the cell stackoverflow.com/questions/1018751/… - Slai
Try this instead: (LEFT(R[2]C[27],13). Placing the Row | Column reference in brackets tells it n rows | columns from current location, instead of absolute. In looking at your code you may need to adjust R[2] to R[1], but you can figure out the appropriate counter to use I am sure. - Scott Holtzman
@ScottHoltzman I considered this but the problem with the code is that the cell's column in which the formula is located is dynamic - so sometimes this would refer to column AA, sometimes to column AC, for instance. However, this made me think of replacing the AA column to be in a fixed offset from the cell the formula's supposed to be in - I'll give it a shot :) Thanks anyway! - Drusio
@Drusio - You could also do the math on the fly. Like n = Offset Column - Column AA. Then ... Left(R[2]C[ & n & "],13)"... - Scott Holtzman
@ScottHoltzman works perfect! Thank you! - Drusio

2 Answers

0
votes

R1C1 reference is usually not needed as relative references are auto-adjusted if you copy+paste the formula or assign it to multiple cells. For example:

Range("A2:A9").Formula = "=CONCATENATE(LEFT(AA2,13), "":"", RIGHT(AA2,5)"
0
votes

If I understand you want the column reference to be always $AA (absolute) while the row reference be relative. You can try this:

ActiveCell.Formula = "=CONCATENATE(LEFT($AA2,13), "":"", RIGHT($AA2,5))"

Then the autofill will adjust automatically the row number while keeping the column at "$AA".

That said, refactor your code to get rid of the select stuff. Look how shorter it will be in addition to using "Explicit references" (just replace "Sheet1" with the actual name of your worksheet):

Sub Copy1()
    Dim rng As Range
    Set rng = Worksheets("Sheet1").Range("A1").End(xlToRight).Offset(1, 1)
    rng.Formula = "=CONCATENATE(LEFT($AA2,13), "":"", RIGHT($AA2,5))"
    rng.Copy rng.Parent.Range(rng, rng.End(xlDown))
End Sub