PDA

View Full Version : put range of cells into variable



saban
06-08-2006, 07:24 AM
Sorry for this stupid question.
How can i put values from cell D2 to D1000 in variable

like:
Startdate = "D2 to D1000"
and
EndDate= "E2:E1000"

And then I need to check if date written into inputbox is between startdate and end date

And it should consider startdate and end date like this
in D2= first start date and in E2 = first end date
and in D3 = second start date and in E3 = second end date

thnx for your answers

Killian
06-08-2006, 08:07 AM
A variable of type "Range" will hold a reference to all your cells.
You can then iterate through the range, checking the value property of each element.
So the code below will act as you describe, displaying a message box for each date range that includes the target date from the input boxDim rngStartDates As Range
Dim myDate As Date
Dim c As Range

'Start dates in col D
Set rngStartDates = Range("D2:D1000")
'End dates in col E, but we can use offset from start date

On Error GoTo errortrap
myDate = Format(InputBox("Enter date"), "dd/mm/yyyy")

For Each c In rngStartDates
If myDate >= c.Value Then
If myDate <= c.Offset(0, 1).Value Then
MsgBox "Found in range:" & vbLf & _
c.Value & " - " & c.Offset(0, 1).Value & vbLf & _
"in row: " & c.Row
End If
End If
Next c

Exit Sub
errortrap:
MsgBox "Invalid entry"

saban
06-08-2006, 08:34 AM
thnx very much fro such a quick reply
but there is one more thing I would need :
This date written in inputbox should actualy be range of dates by range I mean from till like from 22/05/2006 to 24/05/2006 and then it should check how many days this range represents between dates in D and E column(start and end date)-and weekends should be excluded(should not count them)
(I am using datecontrol to put dates in cells)

Thnx for all your help

Killian
06-08-2006, 03:02 PM
So I added the start and end date entry separately, I find the date range and check how many dates in the range are weekdays with a function.
At the moment, I'm expecting a requested date range to fall inside ONE range on the sheet. I'm not quite sure thats what you wanted ?Sub main()

Dim rngStartDates As Range
Dim myStartDate As Date
Dim myEndDate As Date
Dim c As Range

Set rngStartDates = Range("D2:D1000")

On Error GoTo errortrap
myStartDate = Format(InputBox("Enter start date (dd/mm/yyyy):"), "dd/mm/yyyy")
myEndDate = Format(InputBox("Enter end date (dd/mm/yyyy):"), "dd/mm/yyyy")
For Each c In rngStartDates
If myStartDate >= c.Value Then
If myEndDate <= c.Offset(0, 1).Value Then
MsgBox "Found in range:" & vbLf & _
c.Value & " - " & c.Offset(0, 1).Value & vbLf & _
"in row: " & c.Row & vbLf & _
DateDiff("d", myStartDate, myEndDate) & " days" & vbLf & _
CountWeekdays(myStartDate, myEndDate) & " weekdays"
End If
End If
Next c

Exit Sub
errortrap:
MsgBox "Invalid entry"

End Sub

Private Function CountWeekdays(s As Date, e As Date)
Dim i As Long
Dim d As Date
i = 0
Do
d = DateAdd("d", i, s)
If Weekday(d) > 1 And Weekday(d) < 7 Then
CountWeekdays = CountWeekdays + 1
End If
i = i + 1
Loop Until d > e
End Function

jindon
06-08-2006, 06:50 PM
Hi
using array

dim myTbl, x, res, i
res=InputBox("Enter Date") ' <- Format if you need..
myTbl = Range("D2:E1000").Value
For i = 1 To 1000
If res >= myTbl(i, 1) And res <= myTbl(i, 2) Then GoTo Here
Next
MsgBox "Invalid Entry": Exit Sub
Here:
MsgBox "StartDate = " & myTbl(i, 1) & vbLf & "EndDate = " & myTbl(i, 2)
Exit Sub

saban
06-09-2006, 12:37 AM
thnx for such a quick response looks nice will check it out, and let you know

saban
06-09-2006, 01:17 AM
the problem i found here is that if date is:
07/06/200610/06/2006
and then I am write holidays :
08/06/2006 11/06/2006
In this case I dont get msgbox because it is like one day after 10/06/2006
But it should still consider that period from 08/06/2006 to 10/06/2006 is between 07/06/2006 and 10/06/2006

Any ideas how could I deal with that

Actually I need to check how many days (from start and end date) is between dates in D and E
(myStartDate - myEndDate and then it should check how many days from this range is represented in date in D and date in E
ex. if myStartDate = 22/05/2006 and myEndDate = 24/05/2006 and date in D1 = 23/05/2006 and date in E1 = 25/05/2006 now i need to know how many days myStartDate = 22/05/2006 and myEndDate = 24/05/2006 represents in date in D and E I need result 2 (because there are 2 days from myStartDate = 22/05/2006 and myEndDate = 24/05/2006 between D1 = 23/05/2006 and date in E1 = 25/05/2006

Any ideas how could I deal with that and thnx for your time
(I had come up with an idea:
If start date of holidays is > date when job was assigned to a person then consider start date of holiday and deadline (and find out the number of weekdays between them)

If start date of holidays < date when job was assigned to a person then consider "date when job wa sassigned to him and deadline"

But then I would also need that same thing for end date of holidays
so if end date of holidays is < deadline, then consider end date of holidays and get me the nr of workdays between them or
if end date of holidays is > deadline, then consider deadline and give me the nr of workdays between them)

Killian
06-09-2006, 06:57 AM
OK, so if the range entered strats in one range and finishes in another we need to deal with the entered Start and End dates separately, summing the weekdays from each found range.Dim rngStartDates As Range

Sub main()

Dim myStartDate As Date
Dim myEndDate As Date
Dim f As Range ' found range
Dim str As String
Dim lngDayCount As Long

Set rngStartDates = Range("D2:D11")

On Error GoTo errortrap
myStartDate = Format(InputBox("Enter start date (dd/mm/yyyy):"), "dd/mm/yyyy")
myEndDate = Format(InputBox("Enter end date (dd/mm/yyyy):"), "dd/mm/yyyy")

Set f = FindDate(myStartDate)
If f Is Nothing Then
str = "Range not found"
Else
str = "Starts in range: " & f.Value & " - " & f.Offset(0, 1).Value
If myEndDate <= f.Offset(0, 1).Value Then
lngDayCount = CountWeekdays(myStartDate, myEndDate)
Else
lngDayCount = CountWeekdays(myStartDate, f.Offset(0, 1).Value)
Set f = FindDate(myEndDate)
If Not f Is Nothing Then
lngDayCount = lngDayCount + CountWeekdays(f.Value, myEndDate)
End If
End If
str = str & vbLf & "Ends in range: " & f.Value & " - " & f.Offset(0, 1).Value
str = str & vbLf & lngDayCount & " weekdays"
End If

MsgBox str
Exit Sub
errortrap:
MsgBox "Invalid entry"

End Sub

Private Function FindDate(d As Date) As Range
Dim c As Range

For Each c In rngStartDates
If d >= c.Value And d <= c.Offset(0, 1).Value Then
Set FindDate = c
Exit For
End If
Next
End Function

Private Function CountWeekdays(s As Date, e As Date) As Long
Dim i As Long
Dim d As Date
i = 0
Do
d = DateAdd("d", i, s)
If Weekday(d) > 1 And Weekday(d) < 7 Then
CountWeekdays = CountWeekdays + 1
End If
i = i + 1
Loop Until d > e
End Function

saban
06-11-2006, 09:24 AM
there is a problem if I write 21/06/2006 and 23/06/2006 in vacation and in columns D and E are dates 22/06/2006 in D and 25/06/2006 in E it says range not found but days 22/06 and 23/06 are in range of E and D even if just two days. I need that kind of answer do you know how that would be done.

this is the thing i would like to do :

So this means if startdate>than date in column D and enddate>date in column E then take startdate and date in E column and count the number of days between them
thnx

saban
06-12-2006, 01:08 AM
rngStartDates = Range("D2:D1000")

when this variable is passed to function it is always empty and then i get error:
errortrap:
MsgBox "Invalid entry"


Any ideas why

Killian
06-12-2006, 04:31 AM
I'm starting to get the idea...
Maybe it would be easier just to take each day from the input range, check if it's in one of the ranges and then see if its a weekday...Sub main()

Dim myStartDate As Date
Dim myEndDate As Date
Dim f As Range ' found range
Dim str As String
Dim lngDayCount As Long
Dim lngWeekDayCount As Long
Dim d As Date
Dim i As Long

Set rngStartDates = Range("D2:D12")

On Error GoTo errortrap
myStartDate = Format(InputBox("Enter start date (dd/mm/yyyy):"), "dd/mm/yyyy")
myEndDate = Format(InputBox("Enter end date (dd/mm/yyyy):"), "dd/mm/yyyy")
On Error GoTo 0

For i = 0 To DateDiff("d", myStartDate, myEndDate)
d = DateAdd("d", i, myStartDate)
Set f = FindDate(d)
If Not f Is Nothing Then
lngDayCount = lngDayCount + 1
If Weekday(d) > 1 And Weekday(d) < 7 Then
lngWeekDayCount = lngWeekDayCount + 1
End If
Set f = Nothing
End If
Next

str = myStartDate & " - " & myEndDate & vbLf & _
i & " days" & vbLf & _
lngDayCount & " days in range" & vbLf & _
lngWeekDayCount & " weekdays"

MsgBox str
Exit Sub
errortrap:
MsgBox "Invalid entry"

End Sub

saban
06-12-2006, 01:02 PM
That would be right and then check how many days range of these two dates(vacation) represents in date and deadline

I will check your example and let u know how it worked and what else would I need

thnx man for your help really apreciate it
saban