If statement, then sum, replace and delete.

Hi there.. I am having some trouble with an if statement that it is way to complicated for me..

I will link the xls file so that you can try it out, but i'll explain carefully here!
(xls file attached at the end)

What I want for each cell in H.

Please Login or Register  to view this content.

So for example H6 will first be noticed by if, then I want it to sum I5 and I6 as "NewNominal" and sum J5 and J6 as "NewMarketValue"
then replace I5 with "NewNominal" and J5 with "NewMarketValue". Then Delete the row pending(Row6), then move to the next "Pending:" Value!

Anyone that can kick meo ff or maybe complete a code for it?

Regards, C

XLS FILE ATTATCHED: Copy of PoolAccountPending.xls

Try this:

Please Login or Register  to view this content.

Another one.

Please Login or Register  to view this content.

Worked perfect, thank you!

Do you know if it's possible to add a row counter instead of using "m"?

christopherL, AB33's code will work also, unless you intend on having more data below your 2000 rows that you wanted to ignore. It will work better (no change in code needed) if you ever have more than 2000 rows of applicable data though.

Do you know if it's possible to add a row counter instead of using "m"?

Yes, you can as it is a number. My counter is "I"

Hey mate, I used yours to, and it worked really smooth for me. Thank you
I just noticed a second thing I wanted to adjust.. After this if statement is complete, I want to delete the rows with a nominal or market value = 0.

Do I add this in the if statement I have now, or a completely new with?
If you cba you can write it or else I will give it my best

Regards

Bmxfreedom,
It does not matter if the number of rows are 10, or 1m since the condition of testing each row is "Pending".
It is also efficient to use the last non empty row, instead of a fixed row. If the last row in column H is 10, the code has still need to loop 2000 times.

AB33's code should do what I think you mean by row counter.

Thanks all of you, I have never experienced such great hospitality and quick response before! I am astonished, thanks all of you

Originally Posted by AB33

Bmxfreedom,
It does not matter if the number of rows are 10, or 1m since the condition of testing each row is "Pending".
It is also efficient to use the last non empty row, instead of a fixed row. If the last row in column H is 10, the code has still need to loop 2000 times.

Thanks for the extra clarification. I understand this, I just didn't know christopherL's reason for the 5 to 2000 setup. In many of my own cases, I have data in a set of rows that need something like this done, then more data below that need to be ignored. Now that I really think about it though, deleting the pending rows would indicate that isn't the case, unless christopherL meant to just have their contents cleared instead of the entire row deleted.

Try the attached.

Another one.

Perfect, thank you mate!