PDA

View Full Version : usinig netdays in an "if then" code



hwfan123
01-20-2009, 02:53 PM
I need to create a Macro that will do 4 different things. The first step is listed below and I have attached a sample worksheet to use. If anyone could help with this I would greatly appreciate it.

I need to create a code that will search a worksheet for rows highlighted in blue. Once this has been found I need the code to look in the PO delv date cell and if there is info there to put a netdays formula in the second to last column. This needs to loop through the entire workshet and again only the rows highlighted in blue need to be checked.

Schedule DatePO Delv DateShort QtyPO BalanceShort MaterialCommentsVer.MRP Ctlr.Rev DocCROSS PLANT STATUSIORInspPrcTypEXO BALEXP DATEEXP BALEXP DATEPO BalanceUnrPO Delv Date1/19/20094759691600101510C Y N ECO-00Y0F3001/12/20091731/19/20091/15/2009556567343300101820A Y D ECO-00Y0F2301/19/20092868597100Q101510B Y D ECO-00Y10F741/12/2009351/16/20091/16/200962062931410201510A Y N ECO-00Y0F01/16/20091/21/200968068596613201820A Y N ECO-00Y0F01/19/20091/21/20092043162576190201625A N D 14022Y0F0.2251/21/2009643162576190201625A N D 14022Y0F1/23/20090.2251/22/20091/21/20091/8/200920250085068200201860A Y D ECO-00Y0FN1/21/20092013805202W201670A Y D ECO-00Y0F1501/9/2009Y1/21/20092040012100W201625A1 N D 14160Y400F3001/15/2009N1/20/2009458982111301510A Y N ECO-00Y0F1751/12/20097021/16/20092058152000401670A Y N ECO-00Y0F4951/9/20096791/20/20093358159200701670IN Y D ECO-0Y0F294
(http://www.mrexcel.com/forum/profile.php?do=addlist&userlist=buddy&u=109591)

hwfan123
01-20-2009, 03:03 PM
I need to create a code that will search out rows highlighted in blue. Once found the code would look at the cell labeled "PO Delv date", and if there is information there then insert a netdays formula comparing the scheduled due date and the PO delv date cells. I need this code to loop through the entire worksheet

Bob Phillips
01-20-2009, 04:23 PM
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow

If .Cells(i, "A").Interior.ColorIndex = 20 Then

If IsDate(.Cells(i, "B").Value) Then

.Cells(i, "C").Value = Application.Run("ATPVBAEN.XLA!NETWORKDAYS", .Cells(i, "A").Value, .Cells(i, "B").Value)
End If
End If
Next i
End With

End Sub

hwfan123
01-22-2009, 05:46 PM
Thanks very much for the formula I am just starting out still and I appreciate the effort however there was an error in my instructions, I need the NetworkDays results to show in the cell of the last column.
I have put the formula in as an example of how I would like this to look.