Help on combining declared variable and cell formula

Am new in macro. i encountered runtime error trying to develop a sub to
do same work as SUBTOTAL. I had problems on the line the line that has
a combination of declared variable and cell formula:
ActiveCell.FormulaR1C1 = "=RC[-1]+ TOT" Below is the full code.



Dim z As Long
Dim TOT As Double
z = 3
Range("C2").FormulaR1C1 = "=RC[-1]"
TOT = Range("C2").Value

Do While Range("A" & z) <> ""

If Range("A" & z).Value = Range("A" & z - 1).Value Then

'Range("C" & z - 1).FormulaR1C1 = "=RC[-1]+R[-1]C"

Range("C" & z).Select
ActiveCell.FormulaR1C1 = "=RC[-1]+ TOT"

TOT = "=RC[-1]" + TOT
Range("C" & z - 1).FormulaR1C1 = 0

Else
Range("A" & z).Select
ActiveCell.FormulaR1C1 = 0

End If
z = z + 1
Loop

ActiveCell.FormulaR1C1 = "=RC[-1] + TOT"

should be

ActiveCell.FormulaR1C1 = "=RC[-1]" + TOT

Also, I think you want TOT to be a cell range (Dim TOT as Range) rather than
an actual numerical value. TOT = "=RC[-1]" + TOT will throw an error 13
(Type Mismatch), because VBA is seeing "RC[-1]" as a string.

"sylink" wrote:

> Am new in macro. i encountered runtime error trying to develop a sub to
> do same work as SUBTOTAL. I had problems on the line the line that has
> a combination of declared variable and cell formula:
> ActiveCell.FormulaR1C1 = "=RC[-1]+ TOT" Below is the full code.
>
>
>
> Dim z As Long
> Dim TOT As Double
> z = 3
> Range("C2").FormulaR1C1 = "=RC[-1]"
> TOT = Range("C2").Value
>
> Do While Range("A" & z) <> ""
>
> If Range("A" & z).Value = Range("A" & z - 1).Value Then
>
> 'Range("C" & z - 1).FormulaR1C1 = "=RC[-1]+R[-1]C"
>
> Range("C" & z).Select
> ActiveCell.FormulaR1C1 = "=RC[-1]+ TOT"
>
> TOT = "=RC[-1]" + TOT
> Range("C" & z - 1).FormulaR1C1 = 0
>
> Else
> Range("A" & z).Select
> ActiveCell.FormulaR1C1 = 0
>
> End If
> z = z + 1
> Loop
>
>