Error with VB code interacting with Excel

[jeffcravener]

I am using VB6 and Excel 2000

I am getting the following error:

'Object variable or With block variable not set' with the following code, BUT only when the cell value matches the RowName variable:

Public Function doesRowExist(ExcelWorkSheet As Excel.Worksheet, Column As String, RowName As String) As Boolean
Dim blnExists As Boolean
Dim i As Integer

blnExists = False
'on error goto HandleError
For i = 1 To 1000
If ExcelWorkSheet.Range(Column & i).Value = RowName Then
blnExists = True
Exit For
End If
Next

Set ExcelWorkSheet = Nothing
doesRowExist = blnExists

Exit Function
HandleError:
doesRowExist = False
Set ExcelWorkSheet = Nothing
End Function

[jeffcravener]

Public Function doesRowExist(WorkSheet As Excel.WorkSheet, Column As String, RowName As String) As Boolean
    Dim blnExists As Boolean
    Dim i As Integer
    
    blnExists = False
    'on error goto HandleError
    For i = 1 To 1000
        If WorkSheet.Range(Column & i).Value = RowName Then
            blnExists = True
            Exit For
        End If
        
    Next
    
    doesRowExist = blnExists
    
    Exit Function
HandleError:
    doesRowExist = False
End Function

It is occurring on this line:
WorkSheet.Range(Column & i).Value = RowName
but only when it should be true....

[Maldrid]

There is nothing wrong with the code you show. There might be something wrong with the code after it returns from the function. I am guessing you are trying to reference the worksheet still after you come back from the function if the function returns true. But in your function you set the worksheet to equal nothing since you are passing by reference. This is just my guess since there is nothing wrong with the code you show here. I would change your function declaration to pass byVal instead.

Public Function doesRowExist(ByVal ExcelWorkSheet As Excel.Worksheet, Column As String, RowName As String) As Boolean

[Edit]
I just saw what you posted. I tested your code and it worked fine for me and I used what you are using. I am guessing you are doing something wrong before you make your call.

[jeffcravener]

I found out what was wrong...I was setting the wrksheet to nothing after the function....

sorry about that!!!!

[Maldrid]

Its fine, just glad you got it to work.