Passing a cell as range

[USAMax]

I am kind of new to objects in Excel even though I have been programing in it for over 15 years. I thought this would work but I get the error:

Object variable or with block variable not set

I have the line that called this as:

COCell = FindCustomOption(COs, SelTask)

In the calling function I have, "Dim COCell As Range"

Public Function FindCustomOption(Ws As Worksheet, Task As String) As Range
'Created by David Follmann September 21, 2013
'FINDS THE TASK ON THE SPECIFIED WORKSHEET AND RETURNS THE CELL
Const FstDataRow As Integer = 4, ColOff As Integer = 4      'First Data Row and Column Offsets
Dim CRow As Integer, CCol As Integer                        'Current Row and Column
Dim TaskFound
Const CO As String = "Custom Options"                       'Custom Options worksheet
Dim COs As Worksheet                                        'Create object for Custom Options worksheet
    CRow = FstDataRow
    CCol = 2
    Set COs = Sheets(CO)                                    'Point the object to the Custom Options worksheet
    
    Do While COs.Cells(CRow, CCol + 1) <> ""            'NO: Is there an option description to the right of this box?
        Do While COs.Cells(CRow, CCol + 1) <> ""        '    NO: Is there an option description to the right of this box?
            If COs.Cells(CRow, CCol + 1) = Task Then  '        YES: Again, is there an option description to the right of this box in this column?
                FindCustomOption = COs.Cells(CRow, CCol + 1)
                TaskFound = True
            End If
            CRow = CRow + 1
        Loop                                            '        CHECK AGAIN
        CRow = FstDataRow                               '        Move the Current Row back to the First Data Row
        CCol = CCol + ColOff                            '        Advance the Column by the Column Offset
    Loop                                                '    CHECK AGAIN
    If Not TaskFound Then
        FindCustomOption = Cells(1, 1)
    End If
End Function

I am using Excel 2010.

Thanks,

 

[circledchicken]

Hi,

You need to use the Set keyword to assign the object (rather than its default property) to the given variable.
E.g:

Set COCell = FindCustomOption(COs, SelTask)

and,

Set FindCustomOption = COs.Cells(CRow, CCol + 1)
Set FindCustomOption = Cells(1, 1)

 

[USAMax]

Your timing could not have been more perfect! The very second I was going to try to rewrite it I received the email.

I felt like such an idiot, when I read the answer. It is the first time I tried to use it but I still should have thought of that.

 

[circledchicken]

Haha, great. That can be a bit tricky / confusing - I forget to use it sometimes as well.

 

[USAMax]

Can you answer one more question for me. The function returns the cell as a range with the matching value. Currently I have it returning cell A1 when it does not find a match. Is there a better way to return a False using a range?

 

[circledchicken]

I haven't looked through your code in detail, but maybe change the return Type to Variant, then for the A1 case you can say something like:

FindCustomOption = False

You will also need to adjust the code that calls the function accordingly.

 

[USAMax]

That's funny, that is exactly what I was going to do but I did not think a variant could carry the same properties as a Range. I need the cell's value, row and column. Can a variant do all that?

 

[circledchicken]

Yes, see Variant Data Type

Remember, you will still need to use the Set keyword when assigning a range to the Variant as appropriate.

 

[USAMax]

Thank you!