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.

Perhaps I am misunderstanding, but it looks like you enter that `If` statement by checking `Cells(x, y).Value = "CD Sector Average`. If `Cells(x, y).Value` equals `CD Sector Average`, it cannot be an `Integer` right? — Dan Wagner, Jun 22 '15 at 15:07
So in column B it is looking for the names of both of the things. but in column C there are numbers. Column C gets cleared for the Sector average, but I need to use the number in Column C of the count row as how many rows to add the the sum function in the sector average row. I hope that makes sense. So i will be selecting the cell `(x,y+1)` in count for the number I need to use. — Greg, Jun 22 '15 at 15:20

sancho.s ReinstateMonicaCellio · Accepted Answer · 2015-06-22T15:31:44.373

0

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.

edited Jun 22 '15 at 15:31

answered Jun 22 '15 at 15:23

sancho.s ReinstateMonicaCellio

14,708
20
93
185

Will this work if the cell that I am referencing the C3 in the example you have, is changed. So one time it might be C3, another its C5? if not how could I change it to refer to the cell directly to the right of the Cell that says "CD Count" – Greg Jun 22 '15 at 15:30
I edited the answer while you were commenting. As said, you will build the formula in VBA with the appropriate reference (either C3, C5, etc.), so it will work. I changed C3 -> D3 in the update, to conform to your use of column C as the source data for summing. – sancho.s ReinstateMonicaCellio Jun 22 '15 at 15:35
I used your indirect formula but had to change it a bit, because I had to add a row that used the count number to return the row numbers of the top and bottom row numbers of the data that I needed to sum. Thanks for the idea! – Greg Jun 22 '15 at 18:24

score 0 · Answer 2 · edited May 23 '17 at 12:22

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.

Using a stored integer as a cell reference

2 Answers2

Linked