vb excel

[Gibson486]

different problem....

Why does this line not work?

ActiveCell.FormulaR1C1 = "=sum(cells(row_hours, 3): cells(row_hours, countsites))"

I want to tell excel to sum the line of each row.

If i get rid of the quotations and try something like

ActiveCell= sum(range(cells(row_hours, 3): cells(row_hours, countsites))), it keeps thinking that sum is a function call.

 

[WannaFly]

Not sure but I believe that should be Cell(hours_row, hours_col) - nowhere in this code do you have hours_x defined

 

[Gibson486]

Originally posted by: WannaFly
Not sure but I believe that should be Cell(hours_row, hours_col) - nowhere in this code do you have hours_x defined

should be hours_row....but even with that, it still does not run.

 

[Gibson486]

see above for new problem

 

[KLin]

You need to qualify it in double quotations if you're setting it within VBA:

activecell.Value = "=sum(A1:a30"

 

[Gibson486]

Originally posted by: KLin
You need to qualify it in double quotations if you're setting it within VBA:

activecell.Value = "=sum(A1:a30"

I tried that...but the problem is that I need to be able to have it be as general as possible because I will never know if someone will shift cells down. In otherwords, I could code it to always sum up a10 to a100, but even if i tell people not too, i just know someone along the line will shift cells down. It will end up just adding cells that we do not even care to add (or possibly not adding enough of the cells).

That is the reason why i am trying to add the cells by using cells(x,y) command.

 

[KLin]

Are row_hours and countsites supposed to be variables?

 

[Gibson486]

Originally posted by: KLin
Are row_hours and countsites supposed to be variables?

yes

 

[KLin]

ActiveCell.Value = "=sum(range(cells(" & row_hours & ", 3): cells(" & row_hours & ", " & countsites &")))"


Try that.

 

[Gibson486]

Originally posted by: KLin
ActiveCell.Value = "=sum(range(cells(" & row_hours & ", 3): cells(" & row_hours & ", " & countsites &")))"


Try that.

It still just outputs #Name into the excel cell....although.... I like how it actually spits out the cell numbers in the formula.

I think the problem is that excel is just stupid and it just does not know the Cells(x,y) ...or range(...), command (does VB change it to the letter number format?), which is kind of dumb because it's the easist way to iterate through cells.

Oh well...I am not gonna spend too much time on this....if work wants to automate it, i am not gonna do it at the expense of my billable hours.