What do I set the variable res to?

"Tom Ogilvy" wrote:

Find has a hard time finding dates sometimes (in my experience); especially
if you don't manage the options in Find. So you need to see if the find was
successful and then possibly use Match to see if you can find the date.
Dim sStr as String


Set coltrgsdate = srcDate.Find(sDate)
If coltrgsdate Is Nothing Then
sStr = Format(sDate, "mm/dd/yyyy")
res = Application.Match(CLng(CDate(sStr)), srcDate, 0)
If Not IsError(res) Then
Set coltrgsdate = srcDate(1, res).EntireColumn
Else
MsgBox sStr & " , the start date not found, quitting"
Exit Sub
End If
Else
Set coltrgsdate = coltrgsdate.EntireColumn
End If

Set coltrgedate = srcDate.Find(eDate)
If coltrgedate Is Nothing Then
sStr = Format(eDate, "mm/dd/yyyy")
res = Application.Match(CLng(CDate(sStr)), srcDate, 0)
If Not IsError(res) Then
Set coltrgedate = srcDate(1, res).EntireColumn
Else
MsgBox sStr & " , the end date not found, quitting"
Exit Sub
End If
Else
Set coltrgedate = coltrgedate.EntireColumn
End If

Of course, if Find is failing, then you should get an error on
Set coltrgsdate = srcDate.Find(sDate).entirecolumn

since you Nothing doesn't have an entirecolumn


--
Regards,
Tom Ogilvy


"Erik" wrote in message
...
Here is my code again with a few modifications and comments to explain
what
I'm trying to do. I can't figure out why my code is not setting the
coltrgsdate and coltrgedate ranges. My guess is that I'm not setting a
source range or using the find function correctly. Please help.

Option Explicit
Global sdate As Date
Global edate As Date


Sub dostuff()
Dim name As String
Dim req As String
Dim srcName As Range
Dim srcDate As Range
Dim rowtrgname As Range
Dim coltrgsdate As Range
Dim coltrgedate As Range
Dim strg As Range
Dim etrg As Range
Dim lvrng As Range
Dim cell As Range
Dim color As Integer
Dim wks1 As Worksheet


Set wks1 = Worksheets("IP LV Tracker")
'column B from B3 to B88 is the list of names
'I set srcName to the intersect with usedrange in case I need to add
or
'delete names later.
Set srcName = Intersect(wks1.Columns("B"), wks1.UsedRange)
'Row 1 from C1 to DT1 are the calendar dates
'Again set to intersect with usedrange in case I want to change the
'range of dates. I'm not sure if I'm doing this correctly.
Set srcDate = Intersect(wks1.Rows(1), wks1.UsedRange)

'Loop through the input by selecting yes to make as many or as few
'inputs as required.
Do While MsgBox("Do you want to make an input?", vbYesNo) = vbYes

name = Application.InputBox("Enter the last name.")
req = Application.InputBox("Enter LV for leave or SL for
speclib.")
'Global variables sdate and edate are set by userform calendars to
a
date.
startCalendar.Show
endCalendar.Show

Set rowtrgname = srcName.Find(name).EntireRow
'This, I think, is where the problem starts. When I set a watch
'for coltrgsdate and run the sub, it doesn't get set to anything.
'Therefore, I get the object variable not set error. I don't know
what
'I'm doing wrong.
Set coltrgsdate = srcDate.Find(sdate).EntireColumn
Set coltrgedate = srcDate.Find(edate).EntireColumn

Set strg = Intersect(rowtrgname, coltrgsdate)
Set etrg = Intersect(rowtrgname, coltrgedate)

'Here, I want to set a range from the start target(strg) to the
end
'target(etrg).
Set lvrng = wks1.Range(strg, etrg)

For Each cell In lvrng
'I want to make the value of each cell in lvrng = req ie.LV or
SL
Set cell.Value = req
'Select a case for coloring each cell based on value of req.
Select Case req
Case Is = "LV": color = 4
Case Is = "SL": color = 6
End Select
'color the cell.
cell.Interior.ColorIndex = color
Next cell

Loop

End Sub