Absolute Referencing

[klugdav]

Hello, I am trying to develop a template for my company and have just started using VBA. However, after some trial and error I have realized that my macros all use relative referencing. Since my template is constantly changing I would like to use absolute referencing but I do not know how to write this in VBA.

For a simple example, let's say I have a cell A1 has the value 5 and cell B1 has the value 10. Then I write the following macro to add cells A1 and B1 and put it into cell C1

Cells(1,3).FormulaR1C1 = "=R1C1 + R1C2"

However, let's say that now I want to go back and insert a row above this so I can add headers to cells A1 and B1 (which are now cells A2 and B2 after inserting a row). I can't seem to figure out how to write the formula to make an absolute reference to these cells so as I modify the spreadsheet I do not have to keep re-writing the code.

Any helps/tips/ideas would be great. Thanks

 

[AD_Taylor]

So let me rephrase your question.

You don't want to know how to get a macro to place an absolute reference into a cell (via a formula). You have that code which is what you've already posted.

What you want to do is make that line always reference the relevant cell, no matter where it may end up after several redesigns of the sheet. I.e.

Cells(1,3).FormulaR1C1 = "=R1C1 + R1C2"

Then if you've added a row above these cells, you want the macro to instantly know this and change to something like:

Cells(2,3).FormulaR1C1 = "=R2C1 + R2C2"

If this is the right interpretation, then you are most likely best off using named ranges. There are loads of walkthroughs about naming cells/ranges if you Google it so I won't go into depth here. Once you have a named range though, if it moves its cell reference is auto updated. And you can refer to them in VBA.

Try it like this:

Range("namedRngOutPut").Formula = "=" & Range("namedRng1").Address & " + " & Range("namedRng2").Address

Or with range variables as well because I prefer this style
It makes it easier to update the names if you decide to change them at any point.

Dim rng1            As Range
Dim rng2            As Range
Dim rngOut          As Range

Set rng1 = Range("namedRng1")
Set rng2 = Range("namedRng2")
Set rngOut = Range("namedRngOutPut")

rngOut.Formula = "=" & rng1.Address & " + " & rng2.Address

 

[klugdav]

Thanks for the quick response. I think I understand how to do it now, although it looks like I'm going to have to re-write a lot of the code. It's better than changing the workbook layout and having a bunch of incorrect references though.

 

[Andrew Poulsom]

Welcome to MrExcel.

It seems to me that you actually need relative referencing. Then the formula will adjust when you insert/delete rows/columns. Absolute referencing is only relevant when you copy/paste.

 

[klugdav]

I believe absolute referencing is what I need, as potentially I will be moving cells around via copy/cut and paste. I do have one more question though:

Lets say I have a range of cells (named BuildingInput) from from A1:B3 as follows:

<TABLE style="WIDTH: 199pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=265><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 103pt; mso-width-source: userset; mso-width-alt: 5010" width=137><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 103pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=137>Column A </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> Column B</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17> Row 1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>SF</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>500000</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17> Row 2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Cooling Tons/ 1000SF</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17> Row 3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Total Tons</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>500</TD></TR></TBODY></TABLE>

In the range C1:D5 (named LoadOutput) I would like to write a formula that does the following:
-In cells C1:C5 I want to input 95, 85, 75, 65, 55, respectively
-In cells D1:D5 I want to calculate
D1: =B1*B2*B3
D2: =B1*B2*B3*.75
D3: =B1*B2*B3*.50
D4: =B1*B2*B3*.25
D5: =0

My problem is that I am not sure how to reference various cells within a range in VBA. Additionally, if a cell is merged with another cell in a named range, should I reference the top-left cell in the range?

Thanks for the help!