Sum Values From Row Dependent on Adjacent Cell Value

[hobbiton73]

Hi, I wonder whether someone may be able to help me please.

I'm trying to perform the following on the last row of a data table.

• Find the first blank cell in column B starting from row 8, then

• Insert the text value "Summary of B&C, BT, E&C, PT & PD" into this row, i.e creating a 'Summary Row' then

• If the cells in column B contain the values "B&C, BT, E&C, PT or PD" sum the values on the same row from columns C:H and paste the totals in the 'Summary Row'

I've put the following code together which finds the first blank cell and adds the text value "Summary of B&C, BT, E&C, PT & PD". I've then started to add the code which searches for the desired values, but I can't work out how to sum the adjacent values and add the totals to the 'Summary Row'.

Please Login or Register  to view this content.

I just wondered whether someone may be able to look at this please and offer some guidance on how I may complete this.

Many thanks and kind regards

Chris

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[hobbiton73]

Hi @John H. Davis, thank you for taking the time to reply to my post.

I've tried the code you kindly sent and the text value is placed in the correct cell, but unfortunateky the sum function doesn't work, with the cells on the 'Summary Row' only showing zero.

Many thanks and kind regards

Chris

[JOHN H. DAVIS]

Chris: Can you attach a sample where the code does not work. I only tested on some makeshift data.

[hobbiton73]

Hi, please ignore me, the code works perfectly, thank you!

Because the information is confidential, I shortened the values I needed to search for. When I used the code you kindly sent, I forgot to change these values, my sincere apologies.

Many thanks and kind regards

Chris

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback.

[hobbiton73]

Hi, I wonder if someone may be able to help me please.

John H. Davis kindly helped me with the above issue I was having. I initially tested the code and all seemed fine, but further testing has revealed that the summary row figures aren't entirely accuarte. I think it may be because these figures are rounded up or down, so I've been trying to find a way to show the total figure in the format 0.00.

I've tried amending these lines:

`z = z + Cells(i, y).Value` and
`z = z` to

`z = z + Cells(i, y).Value(Format(z, "#,##0.00"))` and
`z = z (Format(z, "#,##0.00"))`

Without success.

I just wondered whether someone could possibly look at this please and let me know where I'm going wrong.

Many thanks and kind regards

Chris

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[hobbiton73]

Hi @John H. Davis, thank you very much for taking the time to reply to my post with the change in code. The format change works great, but unfortunately doesn't change the problem with the 'Total' figures shown. For example in my first column the total should be 12.35, but the code shows the total as 13, so I'm not sure whether it isn't the original code that's the issue. Is there perhaps anyway please, you could suggest a method of making the changes(s) to make the sum total the correct figure?

Many thanks and kind regards

Chris

[JOHN H. DAVIS]

Can you attach a sample with some dummy data?

[hobbiton73]

Hi John H. Davis, I'm sorry for taking a little while to get back to you, but please find attached the sample file.

Many thanks and kind regards

Chris

[JOHN H. DAVIS]

Try:

Please Login or Register  to view this content.

[hobbiton73]

Hi @John H. Davis, thank you very much for taking the time to revisit this, it works great.

May I ask please, could you possibly add some comments to the code. I understand some elements of this not all.

Many thanks and kind regards

Chris