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.

回答1:

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

回答2:

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

回答3:

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 

回答4:

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