Insert dates from a userform

[leonidas]

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!

Please Login or Register  to view this content.

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[leonidas]

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!

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Arif Ali]

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