Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
prompt for weekday and distribute the dates in columns
Is it possible that after clicking on a macro button taht does lot of other
work in a sheet - a prompt box or wateva shud ask me to pick from list the weekdays . say the box should contain a list with ranges like 31-Dec-207 to 4-Jan-2008 7-Jan-2008 to 11-Jan-2008 ... and this range of working days should be till 29-Dec-2008 to 2-Jan-2009 So when i pick one of teh above range , the five days in a week shud distribute in 5 columns say D3,E3,F3,G3,H3 and the ranges include only mon to friday dates and do not include sat and sunday. so whteversystem date im in now , when i picjk from the list i shud get the dates corresponding to teh range only in those 5 columns... pls help |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
prompt for weekday and distribute the dates in columns
yes it can be done. here is a start which displays the range of dates for
the prevvious 6 weeks. Without seeing the rest of your copde I do not know how to connect the date picked from the list to the actual spreadhseet and the previous code that working. Sub displaydates() Const NumofWeeks As Integer = 6 MyDate = Date OffsetMonday = Weekday(MyDate, vbMonday) - 1 StartDate = (MyDate - OffsetMonday) - (7 * NumofWeeks) displaystring = "" For wks = 0 To NumofWeeks FormatDate = Format(StartDate + (7 * wks), "DD-MMM-YYYY") displaystring = displaystring & FormatDate & " to " FormatDate = Format(StartDate + (7 * wks) + 4, "DD-MMM-YYYY") displaystring = displaystring & FormatDate & Chr(10) Next wks MsgBox (displaystring) End Sub "deepika :excel help" wrote: Is it possible that after clicking on a macro button taht does lot of other work in a sheet - a prompt box or wateva shud ask me to pick from list the weekdays . say the box should contain a list with ranges like 31-Dec-207 to 4-Jan-2008 7-Jan-2008 to 11-Jan-2008 ... and this range of working days should be till 29-Dec-2008 to 2-Jan-2009 So when i pick one of teh above range , the five days in a week shud distribute in 5 columns say D3,E3,F3,G3,H3 and the ranges include only mon to friday dates and do not include sat and sunday. so whteversystem date im in now , when i picjk from the list i shud get the dates corresponding to teh range only in those 5 columns... pls help |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
for autoation
'Im am actaully automating a sheet called FTP from some inputs from sheet
called WAS 'The rows in WAS are dynamic. And begins from row 6. In the code j=6.. the cell name is at j=5 'Thr FTP sheet needs to be automatically filled on clicking a macro and the row headers are placed at 3.. teh row entries start at i=4) 'The input details from WAS a Column D: Project Name E: task F: assigned to H: Planned start date I: planned End date J: actual Start K: actual end L: Planned Effort O: Status The output fileds in FTP sheet are Column B: Assigned To C: work item (this is from WAS' Project name_Task i.e, concat of Columns D&E with an _) D,E,F,G,H : these contain monday to friday of a week i.,5 working days of a week What i have to do is when i click on the macro i should get a prompt box with a list asking to select the date range for a working week . say the box should contain a list with ranges like 31-Dec-207 to 4-Jan-2008 7-Jan-2008 to 11-Jan-2008 ... and this range of working days should be till 29-Dec-2008 to 2-Jan-2009 So when i pick one of teh above range , the five days in a week shud distribute in 5 columns say D3,E3,F3,G3,H3 and the ranges include only mon to friday dates and do not include sat and sunday. so whtever current date im in now , i shud be able to pick any date range and get the dates corresponding to the range only in those 5 columns... This is the code that i have witten below Sub automateFTP() After this i have to place the following logic The distribution of the day wise effort will be based on a logic which would check for 8 hours of work on a particular day (say it checks for 8 for an individual(asigned to)). If 8 hours has already been consumed in a particular day then no other task will be planned for the day then it will try to distribute 8 hours for the remaining day , If less than 8 hours has been planned then the new task will be distributed as 8-x where x is the hours for already planned task. I had jsut written the code for the folwing . Can u pls help me as ThisWorkbook task is quite urgent to finish and im unaware of how to do.. 'bringing in ProjectName_taskname and assigned to name from WAS into FTP Sheets("WAS").Activate J = 6 'WAS entries start from 6th row . so j=6 I = 4 'FTP entries start from 4th row. so i=4 While Cells(J, 4).Value < "" If (Cells(J, 15).Value < "Completed") Then Sheets("FTP").Cells(I, 3) = Cells(J, 4).Value & "_" & Cells(J, 5).Value Sheets("FTP").Cells(I, 2) = Cells(J, 6).Value End If I = I + 1 J = J + 1 Wend Sheets("FTP").Activate End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please ignore the prev post and consider this one
"deepika :excel help" wrote: 'Im am actaully automating a sheet called FTP from some inputs from sheet called WAS 'The rows in WAS are dynamic. And begins from row 6. In the code j=6.. the cell name is at j=5 'Thr FTP sheet needs to be automatically filled on clicking a macro and the row headers are placed at 3.. teh row entries start at i=4) 'The input details from WAS a Column D: Project Name E: task F: assigned to H: Planned start date I: planned End date J: actual Start K: actual end L: Planned Effort O: Status The output fileds in FTP sheet are Column B: Assigned To C: work item (this is from WAS' Project name_Task i.e, concat of Columns D&E with an _) D,E,F,G,H : these contain monday to friday of a week i.,5 working days of a week What i have to do is when i click on the macro i should get a prompt box with a list asking to select the date range for a working week . say the box should contain a list with ranges like 31-Dec-207 to 4-Jan-2008 7-Jan-2008 to 11-Jan-2008 ... and this range of working days should be till 29-Dec-2008 to 2-Jan-2009 So when i pick one of teh above range , the five days in a week shud distribute in 5 columns say D3,E3,F3,G3,H3 and the ranges include only mon to friday dates and do not include sat and sunday. so whtever current date im in now , i shud be able to pick any date range and get the dates corresponding to the range only in those 5 columns... After this i have to place the following logic one important thing here. All entries into the FTP sheet should be populated based on teh WAS column "staus", When status < completed only those entries for which status is not compleetd should populate under FTP. The distribution of the day wise effort will be based on a logic which would check for 8 hours of work on a particular day (say it checks for 8 for an individual(asigned to)). If 8 hours has already been consumed in a particular day then no other task will be planned for the day then it will try to distribute 8 hours for the remaining day , If less than 8 hours has been planned then the new task will be distributed as 8-x where x is the hours for already planned task. I had jsut written the code for the folwing . Can u pls help me as ThisWorkbook task is quite urgent to finish and im unaware of how to do.. 'bringing in ProjectName_taskname and assigned to name from WAS into FTP Sheets("WAS").Activate J = 6 'WAS entries start from 6th row . so j=6 I = 4 'FTP entries start from 4th row. so i=4 While Cells(J, 4).Value < "" If (Cells(J, 15).Value < "Completed") Then Sheets("FTP").Cells(I, 3) = Cells(J, 4).Value & "_" & Cells(J, 5).Value Sheets("FTP").Cells(I, 2) = Cells(J, 6).Value End If I = I + 1 J = J + 1 Wend Sheets("FTP").Activate End Sub |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please ignore the prev post and consider this one
This is what i havve so far. You need to create a list box so the dates can
be filled into the box. You need to get the box filled. Then select a date from the box. Sub FillListbox() Const NumofWeeks As Integer = 6 Jan1 = DateValue("Jan 1 " & Year(Date)) NextYear = DateValue("Jan 1 " & (Year(Date) + 1)) OffsetMonday = Weekday(Jan1, vbMonday) - 1 MyDate = Jan1 - OffsetMonday With Sheets("FTP") .ListBox1.Clear Do While MyDate < NextYear displaystring = "" FormatDate = Format(MyDate, "DD-MMM-YYYY") displaystring = displaystring & FormatDate & " to " FormatDate = Format(MyDate + 4, "DD-MMM-YYYY") displaystring = displaystring & FormatDate & Chr(10) .ListBox1.AddItem displaystring MyDate = MyDate + 7 Loop End With End Sub Sub getlistbox() With Sheets("FTP") SelectDate = .ListBox1.Text firstDate = DateValue(Trim( _ Left(SelectDate, InStr(SelectDate, " ")))) For Dayoffset = 0 To 4 .Range("D3").Offset(0, Dayoffset) = _ Format(firstDate + Dayoffset, "DD-MMM-YYYY") Next Dayoffset WASRowCount = 6 'WAS entries start from 6th row . so j=6 FTPRowCount = 4 'FTP entries start from 4th row. so i=4 Do While .Cells(WASRowCount, "D").Value < "" If (.Cells(WASRowCount, "O").Value < "Completed") Then Sheets("FTP").Cells(FTPRowCount, "C") = _ .Cells(WASRowCount, 4).Value & "_" & _ .Cells(WASRowCount, 5).Value Sheets("FTP").Cells(FTPRowCount, "B") = .Cells(WASRowCount, "F").Value End If FTPRowCount = FTPRowCount + 1 WASRowCount = WASRowCount + 1 Loop End With End Sub "deepika :excel help" wrote: "deepika :excel help" wrote: 'Im am actaully automating a sheet called FTP from some inputs from sheet called WAS 'The rows in WAS are dynamic. And begins from row 6. In the code j=6.. the cell name is at j=5 'Thr FTP sheet needs to be automatically filled on clicking a macro and the row headers are placed at 3.. teh row entries start at i=4) 'The input details from WAS a Column D: Project Name E: task F: assigned to H: Planned start date I: planned End date J: actual Start K: actual end L: Planned Effort O: Status The output fileds in FTP sheet are Column B: Assigned To C: work item (this is from WAS' Project name_Task i.e, concat of Columns D&E with an _) D,E,F,G,H : these contain monday to friday of a week i.,5 working days of a week What i have to do is when i click on the macro i should get a prompt box with a list asking to select the date range for a working week . say the box should contain a list with ranges like 31-Dec-207 to 4-Jan-2008 7-Jan-2008 to 11-Jan-2008 ... and this range of working days should be till 29-Dec-2008 to 2-Jan-2009 So when i pick one of teh above range , the five days in a week shud distribute in 5 columns say D3,E3,F3,G3,H3 and the ranges include only mon to friday dates and do not include sat and sunday. so whtever current date im in now , i shud be able to pick any date range and get the dates corresponding to the range only in those 5 columns... After this i have to place the following logic one important thing here. All entries into the FTP sheet should be populated based on teh WAS column "staus", When status < completed only those entries for which status is not compleetd should populate under FTP. The distribution of the day wise effort will be based on a logic which would check for 8 hours of work on a particular day (say it checks for 8 for an individual(asigned to)). If 8 hours has already been consumed in a particular day then no other task will be planned for the day then it will try to distribute 8 hours for the remaining day , If less than 8 hours has been planned then the new task will be distributed as 8-x where x is the hours for already planned task. I had jsut written the code for the folwing . Can u pls help me as ThisWorkbook task is quite urgent to finish and im unaware of how to do.. 'bringing in ProjectName_taskname and assigned to name from WAS into FTP Sheets("WAS").Activate J = 6 'WAS entries start from 6th row . so j=6 I = 4 'FTP entries start from 4th row. so i=4 While Cells(J, 4).Value < "" If (Cells(J, 15).Value < "Completed") Then Sheets("FTP").Cells(I, 3) = Cells(J, 4).Value & "_" & Cells(J, 5).Value Sheets("FTP").Cells(I, 2) = Cells(J, 6).Value End If I = I + 1 J = J + 1 Wend Sheets("FTP").Activate End Sub |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please ignore the prev post and consider this one
hi joel,
Thank you fo rthe code but this re4turns an error at SelectDate = .ListBox1.Text wat cud bethe reason "Joel" wrote: This is what i havve so far. You need to create a list box so the dates can be filled into the box. You need to get the box filled. Then select a date from the box. Sub FillListbox() Const NumofWeeks As Integer = 6 Jan1 = DateValue("Jan 1 " & Year(Date)) NextYear = DateValue("Jan 1 " & (Year(Date) + 1)) OffsetMonday = Weekday(Jan1, vbMonday) - 1 MyDate = Jan1 - OffsetMonday With Sheets("FTP") .ListBox1.Clear Do While MyDate < NextYear displaystring = "" FormatDate = Format(MyDate, "DD-MMM-YYYY") displaystring = displaystring & FormatDate & " to " FormatDate = Format(MyDate + 4, "DD-MMM-YYYY") displaystring = displaystring & FormatDate & Chr(10) .ListBox1.AddItem displaystring MyDate = MyDate + 7 Loop End With End Sub Sub getlistbox() With Sheets("FTP") SelectDate = .ListBox1.Text firstDate = DateValue(Trim( _ Left(SelectDate, InStr(SelectDate, " ")))) For Dayoffset = 0 To 4 .Range("D3").Offset(0, Dayoffset) = _ Format(firstDate + Dayoffset, "DD-MMM-YYYY") Next Dayoffset WASRowCount = 6 'WAS entries start from 6th row . so j=6 FTPRowCount = 4 'FTP entries start from 4th row. so i=4 Do While .Cells(WASRowCount, "D").Value < "" If (.Cells(WASRowCount, "O").Value < "Completed") Then Sheets("FTP").Cells(FTPRowCount, "C") = _ .Cells(WASRowCount, 4).Value & "_" & _ .Cells(WASRowCount, 5).Value Sheets("FTP").Cells(FTPRowCount, "B") = .Cells(WASRowCount, "F").Value End If FTPRowCount = FTPRowCount + 1 WASRowCount = WASRowCount + 1 Loop End With End Sub "deepika :excel help" wrote: "deepika :excel help" wrote: 'Im am actaully automating a sheet called FTP from some inputs from sheet called WAS 'The rows in WAS are dynamic. And begins from row 6. In the code j=6.. the cell name is at j=5 'Thr FTP sheet needs to be automatically filled on clicking a macro and the row headers are placed at 3.. teh row entries start at i=4) 'The input details from WAS a Column D: Project Name E: task F: assigned to H: Planned start date I: planned End date J: actual Start K: actual end L: Planned Effort O: Status The output fileds in FTP sheet are Column B: Assigned To C: work item (this is from WAS' Project name_Task i.e, concat of Columns D&E with an _) D,E,F,G,H : these contain monday to friday of a week i.,5 working days of a week What i have to do is when i click on the macro i should get a prompt box with a list asking to select the date range for a working week . say the box should contain a list with ranges like 31-Dec-207 to 4-Jan-2008 7-Jan-2008 to 11-Jan-2008 ... and this range of working days should be till 29-Dec-2008 to 2-Jan-2009 So when i pick one of teh above range , the five days in a week shud distribute in 5 columns say D3,E3,F3,G3,H3 and the ranges include only mon to friday dates and do not include sat and sunday. so whtever current date im in now , i shud be able to pick any date range and get the dates corresponding to the range only in those 5 columns... After this i have to place the following logic one important thing here. All entries into the FTP sheet should be populated based on teh WAS column "staus", When status < completed only those entries for which status is not compleetd should populate under FTP. The distribution of the day wise effort will be based on a logic which would check for 8 hours of work on a particular day (say it checks for 8 for an individual(asigned to)). If 8 hours has already been consumed in a particular day then no other task will be planned for the day then it will try to distribute 8 hours for the remaining day , If less than 8 hours has been planned then the new task will be distributed as 8-x where x is the hours for already planned task. I had jsut written the code for the folwing . Can u pls help me as ThisWorkbook task is quite urgent to finish and im unaware of how to do.. 'bringing in ProjectName_taskname and assigned to name from WAS into FTP Sheets("WAS").Activate J = 6 'WAS entries start from 6th row . so j=6 I = 4 'FTP entries start from 4th row. so i=4 While Cells(J, 4).Value < "" If (Cells(J, 15).Value < "Completed") Then Sheets("FTP").Cells(I, 3) = Cells(J, 4).Value & "_" & Cells(J, 5).Value Sheets("FTP").Cells(I, 2) = Cells(J, 6).Value End If I = I + 1 J = J + 1 Wend Sheets("FTP").Activate End Sub |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please ignore the prev post and consider this one
As I said in my posting, You need to add a list box to the worksheet
1) Open a new toolbar from worksheet menu: View - Toolbars - Control Toolbox 2) Select Listbox on toolbar and add to worksheet. 3) re-Run the code. "deepika :excel help" wrote: hi joel, Thank you fo rthe code but this re4turns an error at SelectDate = .ListBox1.Text wat cud bethe reason "Joel" wrote: This is what i havve so far. You need to create a list box so the dates can be filled into the box. You need to get the box filled. Then select a date from the box. Sub FillListbox() Const NumofWeeks As Integer = 6 Jan1 = DateValue("Jan 1 " & Year(Date)) NextYear = DateValue("Jan 1 " & (Year(Date) + 1)) OffsetMonday = Weekday(Jan1, vbMonday) - 1 MyDate = Jan1 - OffsetMonday With Sheets("FTP") .ListBox1.Clear Do While MyDate < NextYear displaystring = "" FormatDate = Format(MyDate, "DD-MMM-YYYY") displaystring = displaystring & FormatDate & " to " FormatDate = Format(MyDate + 4, "DD-MMM-YYYY") displaystring = displaystring & FormatDate & Chr(10) .ListBox1.AddItem displaystring MyDate = MyDate + 7 Loop End With End Sub Sub getlistbox() With Sheets("FTP") SelectDate = .ListBox1.Text firstDate = DateValue(Trim( _ Left(SelectDate, InStr(SelectDate, " ")))) For Dayoffset = 0 To 4 .Range("D3").Offset(0, Dayoffset) = _ Format(firstDate + Dayoffset, "DD-MMM-YYYY") Next Dayoffset WASRowCount = 6 'WAS entries start from 6th row . so j=6 FTPRowCount = 4 'FTP entries start from 4th row. so i=4 Do While .Cells(WASRowCount, "D").Value < "" If (.Cells(WASRowCount, "O").Value < "Completed") Then Sheets("FTP").Cells(FTPRowCount, "C") = _ .Cells(WASRowCount, 4).Value & "_" & _ .Cells(WASRowCount, 5).Value Sheets("FTP").Cells(FTPRowCount, "B") = .Cells(WASRowCount, "F").Value End If FTPRowCount = FTPRowCount + 1 WASRowCount = WASRowCount + 1 Loop End With End Sub "deepika :excel help" wrote: "deepika :excel help" wrote: 'Im am actaully automating a sheet called FTP from some inputs from sheet called WAS 'The rows in WAS are dynamic. And begins from row 6. In the code j=6.. the cell name is at j=5 'Thr FTP sheet needs to be automatically filled on clicking a macro and the row headers are placed at 3.. teh row entries start at i=4) 'The input details from WAS a Column D: Project Name E: task F: assigned to H: Planned start date I: planned End date J: actual Start K: actual end L: Planned Effort O: Status The output fileds in FTP sheet are Column B: Assigned To C: work item (this is from WAS' Project name_Task i.e, concat of Columns D&E with an _) D,E,F,G,H : these contain monday to friday of a week i.,5 working days of a week What i have to do is when i click on the macro i should get a prompt box with a list asking to select the date range for a working week . say the box should contain a list with ranges like 31-Dec-207 to 4-Jan-2008 7-Jan-2008 to 11-Jan-2008 ... and this range of working days should be till 29-Dec-2008 to 2-Jan-2009 So when i pick one of teh above range , the five days in a week shud distribute in 5 columns say D3,E3,F3,G3,H3 and the ranges include only mon to friday dates and do not include sat and sunday. so whtever current date im in now , i shud be able to pick any date range and get the dates corresponding to the range only in those 5 columns... After this i have to place the following logic one important thing here. All entries into the FTP sheet should be populated based on teh WAS column "staus", When status < completed only those entries for which status is not compleetd should populate under FTP. The distribution of the day wise effort will be based on a logic which would check for 8 hours of work on a particular day (say it checks for 8 for an individual(asigned to)). If 8 hours has already been consumed in a particular day then no other task will be planned for the day then it will try to distribute 8 hours for the remaining day , If less than 8 hours has been planned then the new task will be distributed as 8-x where x is the hours for already planned task. I had jsut written the code for the folwing . Can u pls help me as ThisWorkbook task is quite urgent to finish and im unaware of how to do.. 'bringing in ProjectName_taskname and assigned to name from WAS into FTP Sheets("WAS").Activate J = 6 'WAS entries start from 6th row . so j=6 I = 4 'FTP entries start from 4th row. so i=4 While Cells(J, 4).Value < "" If (Cells(J, 15).Value < "Completed") Then Sheets("FTP").Cells(I, 3) = Cells(J, 4).Value & "_" & Cells(J, 5).Value Sheets("FTP").Cells(I, 2) = Cells(J, 6).Value End If I = I + 1 J = J + 1 Wend Sheets("FTP").Activate End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i distribute columns of data to separate sheets? | Excel Worksheet Functions | |||
WEEKDAY() function: display TEXT not numeric weekday | Excel Discussion (Misc queries) | |||
How can I calculate dates and skip a specific weekday? | Excel Discussion (Misc queries) | |||
Distribute values evenly in two columns | Excel Discussion (Misc queries) | |||
save prompt for user exit, but no save prompt for batch import? | Excel Discussion (Misc queries) |