EXCEL 2003 Macro to get sum

[coolwater]

Hi,

I am new to macro,

Can anyone tell me what's wrong with the code below?
I need to get sum for every week.ie, this week is sum for week1 to week5, next time it will be week 1 to week6.
However the last line of the code doesn't give me the sum.

Dim m_copycell As String
Sheets("SHEETA").Select
Range("L5").Select
m_copycell = Range("L5").Value
Range("F4").Select
Cells("F4").Formula = "=SUM(B2:m_coypcell")"

Thanks a lot.

[Andrew-R]

The first problem is that your code should be in code tags

Secondly, because the value m_copycell is inside the quotes on the last line it will be treated as a literal value. If you want it to be used as a variable you need:

Please Login or Register  to view this content.

But that will only work if m_copycell contains a valid cell reference.

Thirdly, lines 2, 3 and 5 of your code don't do anything useful and should be deleted.

[coolwater]

The first problem is that your code should be in code tags

Secondly, because the value m_copycell is inside the quotes on the last line it will be treated as a literal value. If you want it to be used as a variable you need:

Please Login or Register  to view this content.

But that will only work if m_copycell contains a valid cell reference.

Thirdly, lines 2, 3 and 5 of your code don't do anything useful and should be deleted.

Thank you for the quick reply, I tried your code,but it still not working, it give me an error message say " type mismatch".
m_copycell is equal to one cell, the idea is every week I just type in up to what line the sum total should be. ie. line B6 is week5, so next week, I will change the value in that cell to B7 to get another week's total.

[coolwater]

Thank you for the quick reply, I tried your code,but it still not working, it give me an error message say " type mismatch".
m_copycell is equal to one cell, the idea is every week I just type in up to what line the sum total should be. ie. line B6 is week5, so next week, I will change the value in that cell to B7 to get another week's total.

[Andrew-R]

So does cell L5 contain the value "B6", or "5" or "Week 5"?

[coolwater]

Yes, it is "B6"

[Andrew-R]

D'Oh, sorry, I missed the obvious - you can't use a string reference with Cells.

Change that final line to:

Please Login or Register  to view this content.

[coolwater]

Hi Andrew_R, I tried this code, but it still not working for me, it said" Application-defined of object defined error thank you

[Andrew-R]

Setting the range that way works for me. Try this:

Please Login or Register  to view this content.

[OnErrorGoto0]

Note the corrected spelling

Please Login or Register  to view this content.

[Andrew-R]

Um, yeah, that would explain it. It's always the simple stuff, isn't it?

[Andrew-R]

Oh, and while I think on, you do realise that this whole thing could be done in a formula in cell F4, don't you:

=SUM(INDIRECT("B2:" & L5))

[OnErrorGoto0]

It's always the simple stuff, isn't it?

That is the only stuff I understand.

[coolwater]

Thanks everyone, it works, either way works, it was a typo. Happy days.