find a date in a range - exact match

[mikemike]

Hello,

I would like to find a date entered by the user and then have that date searched out on another sheet. The other sheet has the full year dates but missing all Saturdays, Sundays and Mondays. I had this working well when all dates of year present, but notice an issue when user entered a date that fell outside of dates listed. This code keeps returning rgFoundstart as empty!!! Why is this happening?

Also, why was it finding dates later in the year and not matching the date exactly requested to be found? Dates on both sheets follow mm/dd/yyyy

Thanks
Mike

datestart = Worksheets("Print Schedule").Cells(1, 2)

Dim rgFoundstart As Range
Set rgFoundstart = Worksheets("Vacation Schedule").Range("g3:nn3").Find(What:=(datestart), LookAt:=xlWhole, LookIn:=xlValues) 'find cell with start date entered by user

'If Err.Number <> 0 Or rgFoundstart Is Nothing Then Exit Sub

ColumnLettersFromRangestart = Split(rgFoundstart.Address, "$")(1) ' obtains column letter for start date
Cells(1, 6) = ColumnLettersFromRangestart

[SamT]

First try
Find(What:=CDate(datestart),,,,)

then try
LookAt:=xlpart,,,,)

[mikemike]

First try
Find(What:=CDate(datestart),,,,)

then try
LookAt:=xlpart,,,,)

Thanks for the prompt response. I have tried it but still get error and rgFoundstart as empty
Set rgFoundstart = Worksheets("Vacation Schedule").Range("g3:nn3").Find(What:=CDate(datestart), LookAt:=xlPart, LookIn:=xlValues)

Any ideas?

[SamT]

WAIT!
but notice an issue when user entered a date that fell outside of dates listed.
Is that a true statement as written? Then that date can NOT be found.

[mikemike]

WAIT!
Is that a true statement as written? Then that date can NOT be found.

I have tried it with a true statement and other variations and does not work. The datestart variable does use a date in vacation schedule sheet that does exist but not working still. It did work with older code which oddly doesn't work now after adapting it to dealing with absent dates
This is what I have now- does not work!!!:

Dim datestart As Date
datestart = Worksheets("Print Schedule").Cells(1, 2)

Dim rgFoundstart As Range
Set rgFoundstart = Worksheets("Vacation Schedule").Range("g3:nn3").Find(datestart) ', LookAt:=xlWhole, LookIn:=xlValues) 'find cell with start date entered by user

If Not rgFoundstart Is Nothing Then
Cells(1, 15) = "ok"
Else
Cells(1, 15) = "not ok"
End If


This is the older code that does work but does not taken into account non existent dates
dateend = Worksheets("Print Schedule").Cells(2, 4)
Dim rgFoundend As Range
Set rgFoundend = Worksheets("Vacation Schedule").Range("g3:nn3").Find(dateend) 'find cell with start date entered by user
ColumnLettersFromRangeend = Split(rgFoundend.Address, "$")(1) ' obtains column letter for start date


Any ideas are greatly appreciated.
Thanks
Mike

UPDATED: When I remove the Worksheets("Vacation Schedule") it works on the current sheet 'Print schedule' I have tried the 'With Sheets("Vacation Schedule") and Sheets("Vacation Schedule").Activate but neither work. How can I access the other sheet to find the date address?

Thanks
Mike

[SamT]

If Not rgFoundstart Is Nothing Then
Cells(1, 15) = "ok"
Else
Cells(1, 15) = "not ok"
End If


This is the older code that does work but does not taken into account non existent datesYes it does

The Line
If Not rgFoundstart Is Nothing Then
Literally means
Only If [Date]Found Then


Why don't you "Go Advanced" and upload/Attach the workbook

[mikemike]

Yes it does

The Line
If Not rgFoundstart Is Nothing Then
Literally means
Only If [Date]Found Then


Why don't you "Go Advanced" and upload/Attach the workbook

Thanks. Ive attached the workbook. The code works but does not access the other sheet whether date exists or not. Jan9 is the date being used. rgFoundstart is always empty regardless. Purpose of code is to find if date in sheet 'print schedule 'exists in sheet 'vacation schedule' and to obtain letter from address.

Thank you for your help!
Mike

[mdmackillop]

Why not use Conditional Formatting in the Date cells to confirm an entered date exists.
=ISNA(MATCH(B1,'Vacation Schedule'!$3:$3,0))

[mikemike]

Why not use Conditional Formatting in the Date cells to confirm an entered date exists.
=ISNA(MATCH(B1,'Vacation Schedule'!$3:$3,0))

Thanks. Ive tried it but it is continually true. I also require the letter of the date found in the other sheet. I already have code written to execute copy data over. How would one access the sheet vacation schedule if this code is in a command button in sheet print schedule?

Dim datestart As Date
datestart = Worksheets("Print Schedule").Cells(1, 2)

'Set startsheet = ActiveSheet
Dim rgFoundstart As Range
With Worksheets("Vacation Schedule")
'Set rgFoundstart = Worksheets("Vacation Schedule").Range("g3:nn3").Find(What:=datestart, LookAt:=xlWhole, LookIn:=xlValues) 'find cell with start date entered by user
Set rgFoundstart = Worksheets("Vacation Schedule").Range("g3:nn3").Find(What:=CDate(datestart), LookAt:=xlPart, LookIn:=xlValues)
If Not rgFoundstart Is Nothing Then
Cells(1, 15) = "ok"
Else
Cells(1, 15) = "not ok"
End If
End With

[SamT]

I refactored the code a bit. I discovered that the file is corrupt.
see:
https://stackoverflow.com/questions/19861288/excel-vba-run-time-error-32809-trying-to-understand-it#29943113
https://superuser.com/questions/874349/excel-runtime-error-32809-corrupt-workbooks


Option Explicit

Private Sub test() 'CommandButton1_Click()
Dim lastrowsheet1_colA As Long
Dim lastrowsheet1_colC As Long
Dim lastrowsheet1_colD As Long
Dim lastrowsheet1_final As Long
Dim rgFoundstart As Range

Dim datestart 'As Date
Dim dateend As Long

Dim ColumnLettersFromRangeend As String
Dim columnletterstart As String
Dim columnletterEnd As String
Dim workingrow As Long
Dim i As Long
Dim therange As String
Dim namerange As String
Dim chk As Long
Dim j As Long

'CLEAR CONTENTS OF PRINT SCHEDULE SHEET'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''
'lastrow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
With Sheets("Print Schedule")
.Range("a4:t150").ClearContents
.Cells(3, 1) = "Last Updated: " & Date
.Cells(4, 1) = "First"
.Cells(4, 2) = "Last"
If Cells(1, 4) > Cells(2, 4) Then MsgBox ("'End Date' must be greater than 'Start Date' Please change")
End With

'FIND LAST ROW IN SHEET1 FOR COLUMN A, C and D''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
With Worksheets("Vacation Schedule")
lastrowsheet1_colA = .Cells(.Rows.Count, "a").End(xlUp).Row
lastrowsheet1_colC = .Cells(.Rows.Count, "c").End(xlUp).Row
lastrowsheet1_colD = .Cells(.Rows.Count, "d").End(xlUp).Row
lastrowsheet1_final = WorksheetFunction.Max(lastrowsheet1_colA, lastrowsheet1_colC, lastrowsheet1_colD) ' find max number of set of last row variables


'START DATE ENTERED - EXTRACT ADDRESS AND COLUMN ADDRESS ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''

datestart = Worksheets("Print Schedule").Cells(1, 2)

Set rgFoundstart = .Range("g3:nn3").Find(datestart, LookAt:=xlPart, LookIn:=xlFormulas)
If Not rgFoundstart Is Nothing Then
Cells(1, 15) = "ok"
Else
Cells(1, 15) = "not ok"
End If
End With

'END DATE ENTERED - EXTRACT ADDRESS AND COLUMN ADDRESS ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''
dateend = Worksheets("Print Schedule").Cells(2, 2)
Dim rgFoundend As Range
Set rgFoundend = Worksheets("Vacation Schedule").Range("g3:nn3").Find(dateend) 'find cell with start date entered by user
ColumnLettersFromRangeend = Split(rgFoundend.Address, "$")(1) ' obtains column letter for start date
Cells(1, 7) = ColumnLettersFromRangeend
Cells(1, 10) = rgFoundend

'FIND IF ROW BETWEEN START DATE COLUMN AND END DATE COLUMN IS NOT EMPTY (COUNTA) AND PASS TO PRINT SCHEDULE SHEET
columnletterstart = "g"
columnletterEnd = "s"

workingrow = 3
For i = 3 To lastrowsheet1_final

therange = columnletterstart & i & ":" & columnletterEnd & i 'string for range
namerange = "C" & i & ":" & "D" & i

With Worksheets("Vacation Schedule")
If Application.WorksheetFunction.CountA(.Range(therange)) > 0 Then 'test to see if range is not empty
.Range(therange).Copy Destination:=Worksheets("Print Schedule").Cells(workingrow + 2, 3) 'copy 'V' to colC
.Range(namerange).Copy Destination:=Worksheets("Print Schedule").Cells(workingrow + 2, 1) 'copy first and last name to ColA
chk = chk + 1
Cells(4, 5) = chk
Else
workingrow = workingrow - 1
j = j + 1
Cells(2, 5) = "empty" & j
End If

Cells(3, 5) = "i =" & i
workingrow = workingrow + 1
End With
Next
End Sub

[mikemike]

Unbelievable!!! thank you so much and to everyone that helped me and the prompt reponses. The file was created possibly before IT updated the computer

Mike

[mdmackillop]

The code works but does not access the other sheet whether date exists or not.

I've tried it but it is continually true.
Something fundamentally wrong then.