how to write sum formula for a range in the last empty cell in each row....

[sktneer]

Hi ExcelForum members,

I am a newbie in excel vba. I am facing a problem in writing a code for the following scenario.

I have a spreadsheet. In this sheet the data is added daily so one column is increased everyday. The rows may also be increased. What I am trying to do is to sum the data in each row for a range of first cell in the row to the last non empty cell in that row and this has to be done for all the rows. So I thought the for loop would be helpful for such requirement and I tried to write the following code. But unable to write the sum formula in the last empty cell in a row and I get the value error. The code is as follows.....

Please Login or Register  to view this content.

Please see the attachment.

Any help would be appreciated. Thanks in advance.

[TMS]

One way:

Please Login or Register  to view this content.

Regards, TMS

[TMS]

Thanks for the rep.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[FDibbins]

A simple non-VBA method would be to have your =SUM() on the left of the data (maybe right next to the description?) and then use =SUM(C3:ZZ3) (or something similar)

[sktneer]

One way:

Please Login or Register  to view this content.

Regards, TMS

Thanks TMS for your reply. It worked perfect. But the problem is as I said that I am a newbie to excel vba so I am not aware of with and end with block.
It would be better if you tell me that how can I put the sum formula in my code. How can I use variables which return the row number and the column number in the sum range within the For loop because I want to put If Else condition for the calculations within the For loop i.e. if certain condition is true then I want this calculation to take place otherwise do something else. Moreover it will be easier for me to understand. Because I can use different formula based on different cells as well. Is there any way to do this?

[TMS]

Please Login or Register  to view this content.

Regards, TMS

[sktneer]

Please Login or Register  to view this content.

Regards, TMS

Perfect solution. Wonderful. This is exactly what I was struggling to find out. Thanks a lot TMS. You are just great.

[TMS]

You're welcome.