Hello,
I am new to Excel and here is what I am having trouble with.
I need to have a date range with a total of days to complete a task. I have used networkdays with some success but I really need to format a entire colum so whe I enter dates it sorts them for me. Example:
My Form has Column C and D, C is the date in and D is the date out. I can manually add the network days to column E for each line but its a Pain in the butt. Any way to do this? Please?
.
Cant you just add network days to column E and drag down?
Problem is we have to enter,delete and change inventory daily I was really hoping for a solution, Thank youOriginally Posted by day92
You could run a Worksheet_Change() event macro that when something is added to column C or D, it auto-fills the NETWORKDAYS formula.
Wow! Not sure how to do that. see where to add a macro but dont how. My Start Date is in Column E Finish Date is in F, the results (Networkdays) is in G. It would be nice if it could exclude lines that have no finish date.
Private Sub Worksheet_Change(ByVal Target As Range) 'Check if the changed cell(s) intersect with column E or F) If Not Intersect(Target, Range("E:F")) Is Nothing Then 'Check if the number of cells changed is 1. If it is more than one, it causes complications. If Target.Count = 1 Then 'Check if column E and F of the changed rell row are both filled in. If Cells(Target.Row, 5) <> "" And Cells(Target.Row, 6) <> "" Then 'Add the formula into column G of the changed cell row. Cells(Target.Row, 7).Formula = "=NETWORKDAYS(" & Cells(Target.Row, 5).Address & ", " & Cells(Target.Row, 6).Address & ")" End If End If End If End Sub
Last edited by Whizbang; 05-25-2011 at 04:22 PM.
Tried it but it needs some brackets or something??
Last edited by shg; 05-25-2011 at 04:33 PM. Reason: deleted quote
It works just fine for me. What error are you getting?
Macro [A] has compilation error
Missing ; before statement at line 1
Private Sub Worksheet_Change(ByVal Target As Range)
Go to "Tools" -> "Macro" -> "Visual Basic Editor".
On the left you should see a list of "modules". If not, go to "View" -> "Project Explorer".
In the Project Explorer, double-click on the module for the worksheet you want to have the autofill code in. It should look like "Sheet1(YourSheetName)"
In the white area to the right (it should take up most of the screen) paste the above code. Do not paste this inside any other existing sub or function.
I use Easy Grid And it just creates a macro. Any ideas?
I don't know what Easy Grid is or how it works. If you open the file in Excel and follow the instructions above, I can assist you with any questions or problems.
Hi,
Easy Gris is just a online Exel spreadsheet. I can do this in Excel and upload it but I dont see the tools
menu
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks