# 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 \$ValueA Labor 10A Material 20A Travel 5A Misc 3A Total Direct 38A Overhead 5A Total 43B Labor 15B Material 25B Travel 10B Misc 5B Total Direct 55B Overhead 10B 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.

See More: Formula based on two conditions

#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 •