CODEWITHSUNDEEP
vbaexcelvariablesformula
Petanek333
Petanek333

Reputation: 29

VBA -- variable in .formula

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.

Upvotes: 1

Views: 146

Answers (4)

Rory
Rory

Reputation: 34035

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...

Upvotes: 3

Vityata
Vityata

Reputation: 43565

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 :)

Upvotes: 0

Wolfie
Wolfie

Reputation: 30047

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

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33662

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 & ")"

Upvotes: 1

Related Questions