Summing Snare

[Bass71]

Hello

In Excel VB, I'm trying to sum the numbers in a column which, depending on previous macros run, the row count will vary. The column has some blanks. I thought I'd try to Dim the row address variable and enter that into my formula:

Dim k as integer
ActiveCell.Value = ActiveCell.Address 'Already at end
ActiveCell(2, 1).Value = Right(ActiveCell.Value, 4)
k = ActiveCell(2, 1).Value - 2 'I thought I could plug this
' into formula

ActiveCell.Formula = "=SUM(R[-k]C:R[-1]C)"

Thanks..............RO

 

[firefytr]

Can you explain exactly what you are trying to sum, it's range and how it is dynamic? Are there are criteria to be met? Is it always the same column? Same sheet? Same book?

-----------
Regards,
Zack Barresse

 

[Bass71]

Same sheet, column and function. The only thing that changes is the number of rows. So, for example first run, I'll need to sum V2:V2000, next V2:V2450 etc. There will be some blank cells as well.

Thanks...........

 

[SkipVought]

Hi,

If you ONLY need the SUM from that column.

Either enter it directly on the sheet or...

    Cells(1, 2).Formula = "=SUM(" & Columns(1).Address & ")"

PS: It's not a good idea to use Activecell, select, activate etc in code. Reference objects directly.

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[firefytr]

To get the range of values in that column starting from row 2, you could use ..

Range("V2", Cells(Rows.Count, 22).End(xlup))

HTH

-----------
Regards,
Zack Barresse