VBA -- variable in .formula

Question

is there a more elegant (simpler) way to put a variable in .formula? I don't want to use .formulaR1C1

I have this code:

Range("C8").Select
Selection.End(xlDown).Select
PosR = ActiveCell.Row
KonR = PosR - 2
Range("N" & PosR).Select
aAddress = Range("$N$9").Address & ":" & Range("$N$" & KonR).Address
ActiveCell.Formula = "=SUM(" & aAddress & ")"

Obviously I want to put =SUM($N$9:$N$101) (101 is the last cell minus 2) into that cell and this code does the job. But I just want to be sure that this is the easiest way to do this.

Rory · Answer 1 · 2017-07-26T09:13:01.300

3

The easiest way is to skip all that selecting and those variables

PosR = Range("C8").End(xlDown).Row
Range("N" & PosR).Formula = "=SUM($N$9:$N$" & PosR - 2 & ")"

Edit: to be more explicit, the easiest way is to use FormulaR1C1 but you said you didn't want to, so...

edited Jul 26 '17 at 09:13

answered Jul 26 '17 at 08:19

Rory

32,730
5
32
35

beat me to it by 37 seconds :) – Shai Rado Jul 26 '17 at 08:20
We should mention that `Range("C8").End(xlDown).Row` will not *always* find the last used row, what I think he really wants, because he said "*101 is the last cell minus 2*". Therefore `Range("C" & Rows.Count).End(xlUp).Row` would be better. – Pᴇʜ Jul 26 '17 at 08:22
@Peh that may or may not be true, but I reckon it would just distract from the actual question anyway, so I ignored it. :) Also, `End(xlUp)` has its own issues. – Rory Jul 26 '17 at 09:14

score 1 · Answer 2 · answered Jul 26 '17 at 08:18

You can use the code below (without using Select and ActiveCell:

PosR = Range("C8").End(xlDown).Row
KonR = PosR - 2

Range("N" & PosR).Formula = "=SUM(" & Range("$N$9").Address & ":" & Range("$N$" & KonR).Address & ")"

Or, the much simplier version:

Range("N" & PosR).Formula = "=SUM($N$9:$N$" & KonR & ")"

score 1 · Answer 3 · answered Jul 26 '17 at 08:21

Well you should be trying to avoid using Select in VBA. You've made the actual inclusion of a variable in the .Formula about a simple as it gets, but your whole code could be simplified:

PosR = Range("C8").End(xlDown).Row
Range("N" & PosR).Formula = "=SUM($N$9:$N$" & PosR - 2 & ")"

Really you should be fully qualifying your ranges too, like so

With ThisWorkbook.Sheets("Sheet1")
    PosR = .Range("C8").End(xlDown).Row
    .Range("N" & PosR).Formula = "=SUM($N$9:$N$" & PosR - 2 & ")"
End With

And if you have blank cells in column C then your use of xlDown will fail to find the last cell. You may want to look at ways of finding the last cell in VBA or simply use

' Again, preferably fully qualified
Range("C" & Rows.Count).End(xlUp).Row

score 0 · Answer 4 · answered Jul 26 '17 at 08:32

Range("$N$9").Address gives exactly "$N$9".

Range("N9").Address gives the same. Thus, it is a bit overwork. Check out the first two debug.print in the sample below.

Thus, once you calculate the last row and assign value to it lngLast, it is possible to get the formula like this: "=SUM(N9:N" & lngLast & ")"

Option Explicit
Public Sub TestMe()

    Dim strA        As String
    Dim lngLast     As Long

    strA = Range("$N$9").Address
    Debug.Print strA = "$N$9"

    strA = Range("N9").Address
    Debug.Print strA = "$N$9"

    lngLast = Range("N" & Rows.Count).End(xlUp).Row - 2
    ActiveCell.Formula = "=SUM(N9:N" & lngLast & ")"

End Sub

Good morning, everyone :)

VBA -- variable in .formula

4 Answers4

Linked