Using a stored integer as a cell reference

Question

Dim x As Integer
Dim y As Integer
For y = 3 To 3
For x = 600 To 1 Step -1
    If Cells(x, y).Value = "CD COUNT" Then
        Cells(x, y).EntireRow.Select
        Selection.EntireRow.Hidden = True
        End if
If Cells(x, y).Value = "CD Sector Average" Then
        Cells(x, y).EntireRow.Select
        Selection.Insert Shift:=xlDown
        Cells(x + 1, y - 1).Select
        ActiveCell.FormulaR1C1 = "=R[0]C[1]"
        Cells(x + 1, y + 1).Select
        Selection.ClearContents
        Cells(x + 1, y + 2).Select
        Selection.ClearContents
        Cells(x + 1, y + 3).Select
        Selection.ClearContents
        Cells(x + 1, y + 4).Select
        ActiveCell.FormulaR1C1 = ***"=sum(R[This is what I need to change]C:R[-3]C"***
        Cells(x + 2, y).Select
    End If

I need to make the starred formula come out as a sum of a column that ends 3 rows above the Sector average row and starts the number that is displayed in a cell in the Count Row.

I tried this to no avail in the count if statement

Dim count As Integer
count = Cells(x , y).Value

And then using the count variable in the cell reference and got an error. Any tips would help or if I'm going about this wrong let me know.

Answer 1

You have to find a suitable formula for entering in the target cell. Then you would build such formula with string concatenation, etc., for entering it via VBA.

One option for the formula is to use OFFSET, as in

=SUM(OFFSET($A$1,D3-1,COLUMN()-1):OFFSET($A$1,ROW()-3-1,COLUMN()-1))

This sums all values from Cell1 to Cell2, in the same column you place the formula. Cell1: at the row indicated by the value in D3, Cell2: 3 rows above the cell that contains the formula.

Another option is to use INDIRECT, as in

=SUM(INDIRECT("C"&D3):INDIRECT("C"&(ROW()-3)))

This sums all values from Cell1 to Cell2, in column C. Cell1: at the row indicated by the value in D3, Cell2: 3 rows above the cell that contains the formula.

Answer 2

You're already using Cells(row, col) to reference your location, so you already know exactly what row you're on. Therefore:

ActiveCell.FormulaR1C1 = "=sum(R[" & x-3 & "C:R[" & X & "]C"

will give you Row("CD Sector Average")-3 through Row("CD Sector Average"). Adjust the x-3 and x as necessary, since I'm not 100% certain which rows you need to total.

Also, now that you've used the Macro Recorder to get your basic code (a great place to start, BTW, but it will teach you terrible coding habits), go read How to avoid using Select in Excel VBA macros to learn how to clean up your code.