using sum function with vba

[cmz21]

Hello,
I would like to sum values in a dynamic column using vba. I have a variable which holds the last row of the dataset. I was trying to use range("A1").formula = "=sumif(ranges using variables)" but this doesn't seem to be working. How Do I use a variable in the sumif.

Any thoughts would be great.

cmz

 

[SkipVought]

hi,

[A1].Formula = "=sum(" & Range(Cells(lFirstRow, "A"), Cells(lLastRowRow, "A")) & ")"

since you never included a CONDITION for the IF condition


Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[AnotherHiggins]

What are the conditions you want to count? If there is more than one condition, then have a look at the SUMPRODUCT function as COUNTIF cannot accommodate more than one.

For detailed explanation of using SUMPRODUCT in this way, see this page.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[cmz21]

after looking it over, I can get away with using the sum function. I don't even need the sumif. But i can't get vba to recognize the variable when I try to set the formula to what I need.

Hope this makes sense

cmz

 

[SkipVought]

HELLOOOooooooo???????????

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[AnotherHiggins]

What variable are you talking about? What do you want to accomplish?

We can't help you if you don't explain what you have already tried (please post the code you have tried), what is giving you problems (where did the code fail) or even what you are trying to do!

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

I have a variable which holds the last row of the dataset.

lFirstRow = 1
lLastRow = 999
[A1].Formula = "=sum(" & Range(Cells([b]lFirstRow[/b], "A"), Cells([b]lLastRowRow[/b], "A")).Address(False,false) & ")"

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[SkipVought]

oops, sorry

lFirstRow = 1
lLastRow = 999
[A1].Formula = "=sum(" & Range(Cells(lFirstRow, "A"), Cells(lLastRow, "A")).Address(False,false) & ")"

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[arbeeone]

Hi,
I have the same basic question. I have tried the code as recommended above by Skip but when I run it in a macro, I am getting the formula come out as =SUM('S13':'S17') (these are the cells I want, but not in quotes). As a result, I'm getting #NAME? in the cell. Any suggestions?

Also, I have a similar addressing problem with the Range command using Cells in that I want to insert rows into a worksheet within a VBA macro. I'm manipulating a large amount of data and wanting to insert subtotals at various points, hence the need to insert the rows. When trying to use the Range method using Cells (i.e. Range(Cells(1,1),Cells(r,1)).Select) I am getting an error. How do I specify the range such that I can insert a new row (or rows) at the desired programmed point?

Any help would be greatly appreciated!

rsb

 

[arbeeone]

Oooops,
I've just tried the formula again only this time using [A1].Formula rather than [A1].FormulaR1C1 and the Sum code worked! - So thanks Skip!!

However, I still have the problem with inserting rows, so I still need help there if possible.

rsb

 

[SkipVought]

rsb,

Since this is a new queston, please post a new thread in the

VBA forum707.

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}