Need code to rearrange select values

Hoping some one can help me out with this find and replace code. I need a script that can find negative value in columns C thru H. The negatives then need to be totalled in column B and replaced with a "0" in there original location. Also values in column I have to be readjusted to correctly sum positive values in columns D thru H. attached is a sample file with orginal data in columns A-I and the desired output in columns M-U. Highlighted rows are the ones that would be adjusted by the script.


Thanks in advance, s4

Hi
The macro on this sheet does what you need.

Enjoy.

It's very close. It still would need some minor adjustments to work the way I need it to. Sorry there is a couple things I should have been more clear about. In some cases there are positive values in columns D-H (like row 43 in the sample I attached), Column I needs to be revalued based on the sum of the positive numbers in columns D-H. Also column B has to sum the negatives in C-H, some cases there can be more than one negative value. Thanks, s4

Ok.

I have amended it as per your rquest.

it still needs a minor tweak, in the case where there are multiple negative and positive values in the same row it doubles the value it places in columns B and I. I changed the sample file to show a couple scenarios where this occurs, see rows 38 & 40. appreciate all your efforts... s4

This is my interpretation.

Please Login or Register  to view this content.

that works correctly, Is there a way to have it return in columns A-I versus off to the right? also any negatives in column C need to be replaced with a 0 and included in the sum in column B also. like the case in row 42.

thanks, s4

Is this what you are asking?

Please Login or Register  to view this content.

Yes it is, that works perfefect, I just needed to make one minor change,

.Offset(, .Columns.Count + 3).Value = a
to
.Offset(, .Columns.Count - 9).Value = a

very nice, thanks again.

You are welcome.

one other question, if I insert columns to the left , for example if columnsA & B are insert as row labels how would I need to modify the script?

If you insert col.A & B and the data/label is somehow connected to the original data range, the data range is now have 2 more columns.
So you will need to adjust the col reference like

Please Login or Register  to view this content.

perfect! thanks again.