Object variable or With block variable not set

[O.B.]

In the function below, I am getting an error when attempting to assign
a value to employeeSheet for the first time. I get the error: "Run-
time error '91': Object variable or With block variable not set". I
am very very new to VBA with Excel. Help?

Private Function GetEmployeeValue(ByVal monthArg As Date, _
ByVal employeeName As String, _
ByVal projectName As String) As
Integer
Dim returnValue As Integer
Dim employeeSheet As Worksheet

returnValue = -1

' Find sheet with name of employee
employeeSheet = Worksheets(employeeName)

' Find row (and column A) in employeeSheet that matches month
For Row = 1 To 100

If employeeSheet.Cells(Row, "A").Value = monthArg Then
' Find column in employeeSHeet that matches sheetName
For Column = 3 To 12
If Column = projectName Then
returnValue = employeeSheet.Cells(employeeRow,
employeeColumn)
Exit For ' Exit Column Loop
End If
Next Column

If returnValue >= 0 Then
Exit For ' Exit Row Loop
End If
End If
Next Row

GetEmployeeValue = returnValue

End Function

 

[Jim Cone]

You have "Column" which is numeric equaling "projectname" which is text.
There is no value established for employeeRow or employeeColumn.
Excel uses"row" and "column" internally, choose other names for your variables.

Add "Option Explicit" (no quotes) as the first line in your module. It forces variable declaration.
When posting a question, it can be helpful to provide the Excel version you are using.
Also provide the line at whch any error occurs.
--
Jim Cone
Portland, Oregon USA
http://tinyurl.com/ExtrasXL

..
..
..

"O.B." <[email protected]>
wrote in message
In the function below, I am getting an error when attempting to assign
a value to employeeSheet for the first time. I get the error: "Run-
time error '91': Object variable or With block variable not set". I
am very very new to VBA with Excel. Help?

Private Function GetEmployeeValue(ByVal monthArg As Date, _
ByVal employeeName As String, _
ByVal projectName As String) As
Integer
Dim returnValue As Integer
Dim employeeSheet As Worksheet

returnValue = -1

' Find sheet with name of employee
employeeSheet = Worksheets(employeeName)

' Find row (and column A) in employeeSheet that matches month
For Row = 1 To 100

If employeeSheet.Cells(Row, "A").Value = monthArg Then
' Find column in employeeSHeet that matches sheetName
For Column = 3 To 12
If Column = projectName Then
returnValue = employeeSheet.Cells(employeeRow,
employeeColumn)
Exit For ' Exit Column Loop
End If
Next Column

If returnValue >= 0 Then
Exit For ' Exit Row Loop
End If
End If
Next Row

GetEmployeeValue = returnValue
End Function

 

[Stefano]

    Dim returnValue As Integer

    Dim employeeSheet As Worksheet

    returnValue = -1

    ' Find sheet with name of employee
    employeeSheet = Worksheets(employeeName)

this should be
Set employeeSheet = Worksheets(employeeName)
because employeeSheet is an object.

Stefano