Need help : Find a particular text and do summation till that point

I have just started learning VBA, mostly from online videos. Could someone help me with this task? workhorse.xlsx This is part of the bigger excel sheet. I would like to write a macro to find the column D6 based on the location of text 'committed total' . Then addup all numbers from E6 to V6 and write total in W6. Then add d6 and w6 and place it in X6.

hi check with the below code

Please Login or Register  to view this content.

Maybe:

Please Login or Register  to view this content.

I need to clarify, this is just small part of excel sheet, columns are added to the left of D as each month passes. Hence the referencing like [-17] or =SUM(E6:V6) will not work.

The end of summation has to be identified based on column header not just absolute location of the cells ie. 'Committed Total'. In short I want to write a macro that selects the numbers between 'committed total' and 'Forecast Total'.

you can find the header with match function in the heading array and form the header you try to make sum of 17 column this will work i think so..

The algorithm is clear in my head. But I do not know how to write the sytanx for it. Started learning VBA a week ago. I do have some experience with C++. I would be very grateful if someone could write the code for me, so that I can learn from it. I tried to find YouTube video for such an algorithm but couldn't find one.

Thanks!

Maybe:

Please Login or Register  to view this content.

This works! Thanks alot. One more bit, how to extend this perform the same task on all the non-empty rows below?

I haven't fully understood, how this code works. first time I am seeing things like Set Rng1 = Rows(2).Find("Committed Total", LookIn:=xlValues, Lookat:=xlWhole) Will have to do some reading on this! :D

Try:

Please Login or Register  to view this content.

You can google or search threads for the Find Method in VBA.

First of all, thanks for this code! Another small issue is D is an absolute reference, shouldn't the location be referenced based on 'committed total' location?

For i = 6 To Range("D" & Rows.count).End(3).Row

Assuming that you have the same amount of rows in each column then it wouldn't be an issue. It would do the same as:

For i = 6 to Activesheet.usedrange.rows.count

Looks like rajansettu came up with a different approach that may work as well.

here is the entire file, so you get an idea about what I am saying

http://ge.tt/8GZxGuq1/v/0

here is the entire file, so you get an idea about what I am saying, I also have to total up all the numbers in top row

http://ge.tt/8GZxGuq1/v/0

Just to be consistent.

Please Login or Register  to view this content.

This is the modified code;

Please Login or Register  to view this content.

actually for i =7, in the complete excel file. row 6 is for summation of values in that column. how do i incorporate that summation?

Can you attach a sample of your layout. To attach a sample in the Forum, push the button with the paperclip (or hit Go Advanced scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

Does this help?

Please Login or Register  to view this content.

Mappe1.xlsx

row 6 is is just summation of column data

Originally Posted by oasafox

Attachment 338246

row 6 is is just summation of column data

Did you try the code posted in Post 18?

Yes. I did. But the last step is to add the columns up and place the total in row 6 cells. the headers in mappe1.xls will give you an idea

Originally Posted by oasafox

Yes. I did. But the last step is to add the columns up and place the total in row 6 cells. the headers in mappe1.xls will give you an idea

Try:

Please Login or Register  to view this content.

This is perfect! Thanks a ton! how to I reward you or give you karma points etc. ? And how to tag this post as SOLVED?

Originally Posted by JOHN H. DAVIS

Try:

Please Login or Register  to view this content.

You're welcome. Glad to help out and thanks for the feedback. To add points just click on the *Add Reputation on my last post. To mark the thread as solved see Forum Rule No. 9.

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.