Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert dates from a userform
Hi, I have a userform with textbox3 and textbox4. The user can insert a startdate in textbox3 and insert an enddate in textbox4. The code below fills cells B10 and below with the dates between the startdate and enddate and the weekends are excluded. The only problem is that by excluding the weekends the enddate automatically changes to a later date. What should I change to get the enddate as last date in column B and still exclude the weekends? Thanks in advance! Code: -------------------- Range("B10").Select With Selection .Value = CDate(TextBox3.Text) .NumberFormat = "dd/mm/yyyy" .AutoFill .Resize(CDate(TextBox4.Text) - CDate(TextBox3.Text) _ + 1), Type:=xlFillWeekdays End With -------------------- -- leonidas ------------------------------------------------------------------------ leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375 View this thread: http://www.excelforum.com/showthread...hreadid=566534 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert dates from a userform
Dim cDays As Long
Dim sFormula As String With Range("B10") .NumberFormat = "dd/mm/yyyy" sFormula = "=SUMPRODUCT(--(WEEKDAY(ROW(" & _ CLng(CDate(TextBox3.Text)) & ":" & CLng(CDate(TextBox4.Text)) & _ "),2)<6))" .Formula = sFormula cDays = .Value .Value = CDate(TextBox3.Text) .AutoFill .Resize(cDays + 1), Type:=xlFillWeekdays End With Not sure that you still want the + 1 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "leonidas" wrote in message ... Hi, I have a userform with textbox3 and textbox4. The user can insert a startdate in textbox3 and insert an enddate in textbox4. The code below fills cells B10 and below with the dates between the startdate and enddate and the weekends are excluded. The only problem is that by excluding the weekends the enddate automatically changes to a later date. What should I change to get the enddate as last date in column B and still exclude the weekends? Thanks in advance! Code: -------------------- Range("B10").Select With Selection .Value = CDate(TextBox3.Text) .NumberFormat = "dd/mm/yyyy" .AutoFill .Resize(CDate(TextBox4.Text) - CDate(TextBox3.Text) _ + 1), Type:=xlFillWeekdays End With -------------------- -- leonidas ------------------------------------------------------------------------ leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375 View this thread: http://www.excelforum.com/showthread...hreadid=566534 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert dates from a userform
Hi Bob, Thanks for your help! I do have another question though. Is it also possible to insert border on the bottom of every row with a friday. The border shoul start from the cell with the date in it (is column B) and the end i variable. The end is the last column with a value in it on the sam row. Hope you can help me with this one! Thanks in advance -- leonida ----------------------------------------------------------------------- leonidas's Profile: http://www.excelforum.com/member.php...fo&userid=3537 View this thread: http://www.excelforum.com/showthread.php?threadid=56653 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert dates from a userform
Dim cDays As Long
Dim sFormula As String Dim iLastCol As Long Dim cell As Range With Range("B10") .NumberFormat = "dd/mm/yyyy" sFormula = "=SUMPRODUCT(--(WEEKDAY(ROW(" & _ CLng(CDate(TextBox3.Text)) & ":" & CLng(CDate(TextBox4.Text)) & _ "),2)<6))" .Formula = sFormula cDays = .Value .Value = CDate(TextBox3.Text) .AutoFill .Resize(cDays + 1), Type:=xlFillWeekdays End With For Each cell In Range("B10").Resize(cDays) If Weekday(cell.Value) = 6 Then iLastCol = Cells(cell.Row, Columns.Count).End(xlToLeft).Column With cell.Resize(, iLastCol - cell.Column + 1).Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThick End With End If Next cell -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "leonidas" wrote in message ... Hi Bob, Thanks for your help! I do have another question though. Is it also possible to insert a border on the bottom of every row with a friday. The border should start from the cell with the date in it (is column B) and the end is variable. The end is the last column with a value in it on the same row. Hope you can help me with this one! Thanks in advance! -- leonidas ------------------------------------------------------------------------ leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375 View this thread: http://www.excelforum.com/showthread...hreadid=566534 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert dates from a userform
Bob,
I was just browsing through some other issues I'd posted and saw your solution. I had proposed the following in a separate post, but I didnt dissect or try your code. How does this compare to yours? Which is preferable/more efficient if any? Thanks, ______________________________ leonidas, It's a little more work than your approach, but it works, and you should be able to adapt this to your needs... Private Sub Dates() Dim NumDays, DayOfWeek, CurrentRow, i As Integer Dim StartD, EndD As Date StartD = CDate(Textbox3.text) EndD = CDate(Textbox4.text) CurrentRow = 10 'Numdays is the actual number of days NumDays = DateDiff("d", StartD, EndD) For i = 0 To NumDays 'Use function to identify ordinal day of week with Monday = 1 DayOfWeek = Weekday(DateAdd("d", i, StartD), vbMonday) 'if day of week is not Sat(6) or Sun(7) then write the date and increment row If DayOfWeek < 6 And DayOfWeek < 7 Then Range("B" & CStr(CurrentRow)).Value = DateAdd("d", i, StartD) CurrentRow = CurrentRow + 1 End If 'check next date in range Next i End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UserForm to insert new data | Excel Discussion (Misc queries) | |||
Insert dates from a userform | Excel Programming | |||
Insert a date from a userform | Excel Programming | |||
Insert a date from a userform | Excel Programming | |||
Insert row if box on userform is checked | Excel Programming |