Change part of copied formula from relative to absolute.

[John]

Simplified version of my problem:
I have formulas in cells in A1 through A6 as follows:
A1 =B1/B11
A2 =B2/B11
A3 =B3/B11
A4 =B4/B11
A5 =B5/B11
A6 =B6/B11

I can't change the B11 to $B$11 or B$11 (it's a long story). I have a macro
that inserts rows in the above range, say between A3 and A4. The macro
copies the formula from A3 to A4, but returns the relative formula =B4/B13.
The B4 is correct, I want that relative, but I want the B13 to be B12 in the
newly inserted row. How do I copy that part of the formula absolutely?

 

[OssieMac]

Hi John,

The easy way is to use absolute addressing but you have said that is out of
the question.

Therefore not sure if this will help but what about naming the cell B11 and
then using the name in lieu of the address. See Defined name in help for
naming cells. However, using named cells is a form of absolute addressing. If
cell is named MyCell the formula would be like the following.
=B1/MyCell

The next way is naming the cell B11 and then using code to build a relative
formula instead of using the name in the formula.

Firstly name the cell B11 as MyCell.

The following code inserts a row and then creates a relative formula from
the cell above and the named cell.

Suggest you test it with some values like in your post in a new workbook
before deciding to use it.

Sub CreateNewFormula()

Dim strFormula As String
Dim rowToInsert As Long

'Assign the number of the row to insert to a variable
rowToInsert = 4

'Insert the row
Rows(rowToInsert).Insert Shift:=xlDown

'Copy formula from cell above inserted row to _
cell in new row (It won't be correct at this point)
Cells(rowToInsert - 1, "A").Copy Cells(rowToInsert, "A")

'Assign the first part of the formula up to _
division sign to a string variable.
strFormula = Left(Cells(rowToInsert, "A").Formula, _
InStr(1, Cells(rowToInsert, "A").Formula, "/"))

'Create formula in cell in new row by concatenating _
strFormula with relative address of named range
Cells(rowToInsert, "A") = strFormula & Range("MyCell").Address(0, 0)

End Sub

The above could probably be done by extracting the last part of the formula
from the previous row and concatenating that with StrFormula.

Hope it helps.

 

[NOPIK]

Or:
Range.FormulaR1C1 = "=R1C1" 'Absolute style
Range.FormulaR1C1 = "=RC[-1]" 'Relative style

 

[John]

Maybe I should explain why I think the absolutes won't work, in case there
is a better solution. I have an Excel Table formed of many groups, each
group with a subtotal row. Column A of the Table, for example is as follows:



Row

1 Data

2 Data

3 Data

4 =Sum A1:A3, (among other formulas)



I have a macro to add a group to my table, which copies formulas for my
subtotal rows from another sheet. The formulas on that other sheet are
relative, since it wouldn't copy properly from another location if they
were. Everything works fine until I need to insert a row in a group, which
I made a macro for, which caused my about/relative problem described
earlier. I need to think out of the box, how else can I do this?

 

[John]

This R1C1 may be my answer, but I'm unfamiliar with it and don't understand
how to use it. Any suggestion please?