Formula based on two conditions

Microsoft Office excel 2003
November 9, 2010 at 17:46:10
Specs: Windows XP
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 65

and 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)
Wend

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


See More: Formula based on two conditions

Report •

#1
November 10, 2010 at 03:45:07
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.


Report •

#2
November 10, 2010 at 08:37:10
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

Report •

#3
November 10, 2010 at 09:28:01
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 


Report •

Related Solutions

#4
November 10, 2010 at 11:28:57
Sorry the "If rng.Value = "DIRECT" Then" should be "If rng.Value = "Total Direct" Then"

Report •

#5
November 10, 2010 at 12:53:22
re: I have figured out how to add a row for each total direct row and give it a name of non-labor direct

You 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 huge difference 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 this How-To.


Report •

#6
November 10, 2010 at 13:06:02
Thank you, it can be a hard coded number as the values will not change.

Report •

#7
November 10, 2010 at 13:19:18
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 this How-To.


Report •

#8
November 10, 2010 at 17:35:59
Thanks!

Report •

Ask Question