Microsoft Office excel 2003

I have an excel worksheet that is a data dump from a report. It contains contractor info and cost info and I want to add one more cost type info to the report.

I have for example:

Contract Cost Type $Value

A Labor 10

A Material 20

A Travel 5

A Misc 3

A Total Direct 38

A Overhead 5

A Total 43

B Labor 15

B Material 25

B Travel 10

B Misc 5

B Total Direct 55

B Overhead 10

B Total 65and continues on similar to the above.

I want to add a row for non-labor direct cost then calculate that value based on total direct minus labor. I have figured out how to add a row for each total direct row and give it a name of non-labor direct but I still need to calculate the cost field in a macro preferably.

This is what I have for inserting a row:Dim rng As Range

Set rng = Range("B17")

While rng.Value <> ""

If rng.Value = "DIRECT" Then

rng.EntireRow.Insert

rng.Offset(-1, 0) = "Non-Labor Direct"

Set rng = rng.Offset(1)

End If

Set rng = rng.Offset(1)

WendI am hoping I can get someone to help me with the calculation part.

It is hard to tell what columns your data is in. Save us some detective work and repost your table (and code) by following he instructions in this

How-To.Thanks.

Hope this helps: Contract Cost_Type $Value A Labor 10 A Material 20 A Travel 5 A Misc 3 A Total Direct 38 A Overhead 5 A Total 43 B Labor 15 B Material 25 B Travel 10 B Total Direct 50 B Overhead 10 B Total 60

I need a row for each contract giving me non-labor direct cost and by a calculation of direct minus labor by contract. I was thinking if/and statement but not quite sure how to work that unless I do one for each contract.

Thanks

Based on your latest post, I don't see how your macro does anything. When is this line ever TRUE?

If rng.Value = "DIRECT" Then

Sorry the "If rng.Value = "DIRECT" Then" should be "If rng.Value = "Total Direct" Then"

re: I have figured out how to add a row for each total direct row and give it a name of non-labor directYou have not "added a row and given it a name".

You have added a row and placed a text string in Column B of that row.

From an Excel perspective, there's a

hugedifference between those 2 items. Look up Named Range in the Excel Help files to see what I mean.Question: Can the value for Total Direct be a hard number or does it have to be a formula?

In other words, can we let VBA calculate the value based on the existing numbers and then place that number in the cell or do you need it to be a formula so that the numbers can be changed later on and the Total Direct value will reflect those changes?

Please note:Before posting Data or Code, read thisHow-To.

Thank you, it can be a hard coded number as the values will not change.

Try this, keeping in mind that it works on the example data that you posted. If what you posted isn't really what you have in your spreadsheet, it might not work. Sub Add_ndTotal() Set rng = Range("B17") While rng.Value <> "" If rng.Value = "Total Direct" Then rng.EntireRow.Insert rng.Offset(-1, 0) = "Non-Labor Direct" rng.Offset(-1, 1) = ndTotal ndTotal = 0 Set rng = rng.Offset(1) ElseIf rng.Value <> "Labor" And rng.Value <> "Total" Then ndTotal = ndTotal + rng.Offset(0, 1) End If Set rng = rng.Offset(1) Wend End Sub

Please note:Before posting Data or Code, read thisHow-To.

Thanks!

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History