use variable in excel formula

问题:

I'm trying to make an automated sum based on an increasing number of rows.

Here is my sample of code but I'm stuck as I don't know the syntax to include a variable in the formula of a cell.

Sheets("Orderboek").Select

Range("H" & (rOi + 1)).FormulaR1C1 = "=Sum(H3:H"&rOi&")"

My variables are declared as follows:

Dim i As Integer, a As Range    'i= index a een gebied
Dim prText As String    'product text
Dim rOi As Long         'rij nummer in orderboek
Dim rng, sumrng As Excel.Range
Dim r As Long           'rij number in offerte
Dim Tot As Long         'totaal som van offerte
Dim ws As Excel.Worksheet

Thanks very much if you can help me out.

回答1:

Try this:

Range("H" & (rOi + 1)).Formula = "=Sum(H3:H" & rOi & ")"

Make sure you use .Formula and not .FormulaR1C1 in this case. Also make sure that there are spaces between ampersands.

回答2:

Note that FormulaR1C1 is useful when you want to refer to a certain point and would like to enter row and cell coordinates. Since you know the column you want to work with, using Formula is better. i'll delete soon; just can't comment yet (<50rep)

*edit: so @timthebomb is spot-on. :)