Error with Find

To briefly describe what I am trying to accomplish, I have a few employees who need to enter their time into a timesheet. The timesheet is by date, employee name, and client number. I have a separate sheet for each client number.

Time is not entered too often, so I just have simple message boxes to call the name, date, and number of hours, then it will enter it into a range. The problem I am having is when trying to determine the column number of the date and row number of the employee. Here is an excerpt of the code. By the way, I am a beginner, so bear with me please.

I am receiving run-time error 1004: unable to get the find property of the range class.

Sub TimeEntry()

    Dim AuditorName As String, AuditorRange As Range, ProvNo As String, InputDate As Date, InputTime As Double, InputRange As Range, DateRange As Range, nCol As Integer, nRow As Integer, dDate As String
    
    AuditorName = InputBox("Enter your name is it appears on the Production Log.", "Associate Name")
    ProvNo = InputBox("Enter the IL provider number.", "Provider Number")
    InputDate = InputBox("Enter the date for which you would like to record time.", "Date")
    InputTime = InputBox("Enter the number of hours to record for this date.", "Hours")
    
    Let Sheets("Time_" & ProvNo).Cells(18, 1).Value = UCase(AuditorName)
    Let Sheets("Time_" & ProvNo).Cells(19, 1).Value = InputDate
    Let Sheets("Time_" & ProvNo).Cells(20, 1).Value = InputTime
    
    Set InputRange = Sheets("Time_" & ProvNo).Range("B1:GA13")
    Set AuditorRange = Sheets("Time_" & ProvNo).Range("B2:B13")
    Set DateRange = Sheets("Time_" & ProvNo).Range("C1:GA1")
    dDate = Format(Sheets("Time_" & ProvNo).Cells(19, 1), "mm/dd/yyyy")
    
'THIS IS WHERE THE PROBLEM OCCURS:
    nCol = DateRange.Find(what:=Range("A19")).Column
    nRow = AuditorRange.Find(what:=Range("A18")).Row
    
    Let Sheets("Time_" & ProvNo).Cells(nRow, nCol).Value = InputTime

End Sub

Any suggestions? Thanks!

By the way, this is in Excel 97.

Originally Posted by Bryan Hessey

Ho,

without setting up a test book, first guess would be that you need

ActiveSheet.select

before that nCol =

if not re-post and I will test.

hth
---

It looks like that helped me get past that hurdle. However, now I have another error:

Object variable or With block variable not set.

This occurs on the same line of code.

Thanks for the input so far.

Originally Posted by Bryan Hessey

good, because I couldn't duplicate the error.

You have a name that starts with a .

ie
.Range(

you need to remove the . or use a With to define it.

hth
---
just noted the '97

Ok, you may need to help me along a little bit. I tried changing some code based on your explanation above, but I don't think I did this properly. See the portion I changed below.

I changed FROM:

    Set InputRange = Sheets("Time_" & ProvNo).Range("B1:GA13")
    Set AuditorRange = Sheets("Time_" & ProvNo).Range("B2:B13")
    Set DateRange = Sheets("Time_" & ProvNo).Range("C1:GA1")

'...
    
    nCol = DateRange.Find(what:=Range("A19")).Column
    nRow = AuditorRange.Find(what:=Range("A18")).Row

I changed TO:

    Dim r As Range
    Dim s As Range
    Dim t As Range
    
    Set r = Range("B1:GA13")
    Set s = Range("B2:B13")
    Set t = Range("C1:GA1")
    
'...

    Sheets("Time_" & ProvNo).Select
    nCol = Sheets("Time_" & ProvNo).t.Find(what:=Range("A19")).Column
    nRow = Sheets("Time_" & ProvNo).s.Find(what:=Range("A18")).Row

Now getting an 'Object doesn't support this property or method' error (same line of code).

I didn't stump you, did I, Bryan?

Just to update, THANKS MSP77079! Based on your response in another thread, I was able to fix my problem.

http://www.excelforum.com/showpost.p...34&postcount=5

Using your reply, I came up with the following (abbreviated):

    AuditorName = InputBox("Enter your name is it appears on the Production Log.", "Associate Name")
    matchRow = 0
    On Error Resume Next
    matchRow = Application.WorksheetFunction.Match(UCase(AuditorName), Range("B1:B13"), 0)
    If matchRow = 0 Then
        AuditorName = InputBox("The name entered does not match the Production Log.  Please re-enter your name.", "Name Error")
        On Error Resume Next
        matchRow = Application.WorksheetFunction.Match(UCase(AuditorName), Range("B1:B13"), 0)
        If matchRow = 0 Then
            rowError = MsgBox("Please check your name and try again.  No time has been recorded." & Chr(13) & Chr(13) & "Contact XXX for assistance.", vbOKOnly, "Name Input Error")
            Exit Sub
        End If
    End If

    ProvNo = InputBox("Enter the IL provider number (14-XXXX).", "Provider Number")
    matchProvNo = 0
    On Error Resume Next
    matchProvNo = Application.WorksheetFunction.Match(ProvNo, Range("B1:B7"), 0)
    If matchProvNo = 0 Then
        ProvNo = InputBox("The provider number does not match the Production Log.  Please re-enter the provider number in the ""14-XXXX"" format.", "Provider Number Error")
        On Error Resume Next
        matchProvNo = Application.WorksheetFunction.Match(ProvNo, Range("B1:B7"), 0)
        If matchProvNo = 0 Then
            ProvNoError = MsgBox("Please check the provider number and try again.  No time has been recorded." & Chr(13) & Chr(13) & "Contact XXX for assistance.", vbOKOnly, "Provider Number Input Error")
            Exit Sub
        End If
    End If

    InputDate = InputBox("Enter the date for which you would like to record time.", "Date")
    Sheets("Time_" & ProvNo).Select
    Let Cells(25, 1).Value = InputDate
    dDate = Format(Cells(25, 1), "mm/dd/yyyy")
    matchCol = 0
    On Error Resume Next
    matchCol = Application.WorksheetFunction.Match(dDate, Range("A1:GA1"), 0)
    If matchCol = 0 Then
        InputDate = InputBox("The date entered must be between 1/1/2007 and 6/30/2007.  Please re-enter the date.", "Date Error")
        Let Cells(25, 1).Value = InputDate
        dDate = Format(Cells(25, 1), "mm/dd/yyyy")
        On Error Resume Next
        matchCol = Application.WorksheetFunction.Match(dDate, Range("B1:B13"), 0)
        If matchCol = 0 Then
            rowError = MsgBox("Please check the date and try again.  No time has been recorded." & Chr(13) & Chr(13) & "Contact XXX for assistance.", vbOKOnly, "Date Input Error")
            Exit Sub
        End If
    End If

    Cells(matchRow, matchCol).Value = InputTime