hey everybody

hoping someone can help with VBA syntax:

I want to use the variable x as a row number reference,

 'Insert Total calculation
    x = 0
    Range("H1").Select
    'go the end of the column
    Do While IsEmpty(ActiveCell.Offset(0, 0)) = False
        x = x + 1
        ActiveCell.Offset(1, 0).Select
    Loop

I have tried the two statements below but they seem to be illegal.

 ActiveCell.FormulaR1C1 = "=SUM(R[ActiveCell.Offset(0,0)]C:R[ActiveCell.Offset(-&x,0)]C)"

ActiveCell.FormulaR1C1 = "=SUM(R[-&x]C:R[-1]C)"


Any help appreciated
thanks

dabith,

Think you should probably consider the different ways of addressing cells. For instance, you can go to cells C1 with either of the following commands:

                 Range("C1").Select
                 Cells(1, 3).Select

The 2nd example is in the form Cells(Row,Col).Select -- which has the advantage of being able to set up a numeric loop with no trouble. For instance, if you wanted to select (sequentially) all of the cells in column C from row 1 to 100, you'd simply create a For/Next loop such as:
    For i = 1 to 100
        Cells(i, 3).Select
    Next

To get more examples of this, suggest you see the page referenced below:

CarlR
www.vba-programmer.com

Also, if for some reason you have an empty cell in the middle of your col H, your loop would stop there.
Try this one:

Dim Lst As Long
Dim Rng As Range

' the last cell in col H that has a value
Range("H65536").End(xlUp).Select

'save the rowNo
Lst = ActiveCell.row

'Define the range
Set Rng = Range("H1:H" & Lst)

'Go to cell that should contain the sum
ActiveCell.Offset(1, 0).Select

'Calculate the sum
ActiveCell.Formula = "=SUM(" & Rng.Address & ")"

tnx carlR.U just made my day. i ve been using macro for quite some time now, but this variable cell selection dint occur to me.
i was able to use it for range selection like - Range("Bi:AAi").Select, but not a particular cell.