Error: Object variable or Block variable not set

[cloh]

Thanks to all the people who reply so promptly to my questions! I have another one related to the form I am working on. When I try to call this function from another, I get the "Object variable or Block variable not set" error... but when I paste it as a macro in Excel I have no problem... could anyone please help me explain this?

The error happens at the Cells.Find line - starting on line 7. Thanks in advance.

Function Finder(DateSelect As String) As Integer
    Dim egg As String
    Dim colval As Integer
 
    egg = "AmtU_" & DateSelect
 
     Cells.Find(What:=egg, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
           xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
           .Activate
    Finder = ActiveCell.Column
    Exit Function
End Function

[FishVal]

Thanks to all the people who reply so promptly to my questions! I have another one related to the form I am working on. When I try to call this function from another, I get the "Object variable or Block variable not set" error... but when I paste it as a macro in Excel I have no problem... could anyone please help me explain this?

The error happens at the Cells.Find line - starting on line 7. Thanks in advance.

Function Finder(DateSelect As String) As Integer
    Dim egg As String
    Dim colval As Integer
 
    egg = "AmtU_" & DateSelect
 
     Cells.Find(What:=egg, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
           xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
           .Activate
    Finder = ActiveCell.Column
    Exit Function
End Function

Hi, cloh.

Keep in my mind that the code is not in Excel. ;)
Properties like "Cells", "ActiveCell" available in Excel module without referencing as long as they are the properties of Excel.Application class.
In Access module you need to reference them through Excel.Application object obtained on excel opening via CreateObject or GetObject.

[cloh]

Hi FishVal,

You're right of course. Thanks for being patient with this newbie :-)

I tried to reference the properties using the Excel.Application object like you said... I still have a problem with the "Cells" property. My code is as shown below. All I want is to search for the string egg in the first row of each worksheet.

Function Finder(DateSelect As String) As Integer
    Dim egg As String
    Dim colval As Integer
    Dim xlapp As excel.Application
    Dim xlbook As excel.Workbook
    Dim xlsheet As excel.Worksheet
    Dim xlrange As excel.range
    Dim ExcelObject As Object
 
    Set xlapp = CreateObject("Excel.Application")
    Set xlbook = xlapp.Workbooks.Add
    Set xlsheet = xlbook.ActiveSheet
 
    xlsheet.range("A1").Select        
    egg = "AmtU_" & DateSelect
    xlsheet.Cells.Find(What:=egg, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
           xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
           .Activate
    Finder = ActiveCell.Column
    Exit Function
End Function

[cloh]

actually nevermind! as soon as i took away the "xlsheet" from line 16 my code worked... but if you can explain it to me, then at least i'll learn something! :-)

[FishVal]

actually nevermind! as soon as i took away the "xlsheet" from line 16 my code worked... but if you can explain it to me, then at least i'll learn something! :-)

:)
Actually I don't know what to explain.
I really don't understand why it works without reference to Worksheet object.
The referencing problem I still see in the code is implicit call of ActiveCell property in line #16, #19.

[cloh]

You're right, FishVal... the problem still exists... this is the code as it stands and the same error still appears. I'm not quite sure why...

Function Finder(DateSelect As String) As Integer
    Dim egg As String
    Dim colval As Integer
    Dim xlapp As excel.Application
    Dim xlbook As excel.Workbook
    Dim xlsheet As excel.Worksheet
    Dim xlrange As excel.range
    'Dim xlcell As
    Dim ExcelObject As Object
 
    Set xlapp = GetObject(, "Excel.Application")
    Set xlbook = xlapp.Workbooks.Add
    Set xlsheet = xlbook.ActiveSheet
 
    xlsheet.range("A1").Select
 
    egg = "AmtU_" & DateSelect
    'MsgBox "this is egg " & egg
 
    Cells.Find(What:=egg, LookIn:=xlValues, LookAt:= _
           xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
           .Activate
    Finder = ActiveCell.Column
 
    Exit Function
End Function

[FishVal]

You're right, FishVal... the problem still exists... this is the code as it stands and the same error still appears. I'm not quite sure why...

Function Finder(DateSelect As String) As Integer
    Dim egg As String
    Dim colval As Integer
    Dim xlapp As excel.Application
    Dim xlbook As excel.Workbook
    Dim xlsheet As excel.Worksheet
    Dim xlrange As excel.range
    'Dim xlcell As
    Dim ExcelObject As Object
 
    Set xlapp = GetObject(, "Excel.Application")
    Set xlbook = xlapp.Workbooks.Add
    Set xlsheet = xlbook.ActiveSheet
 
    xlsheet.range("A1").Select
 
    egg = "AmtU_" & DateSelect
    'MsgBox "this is egg " & egg
 
    Cells.Find(What:=egg, LookIn:=xlValues, LookAt:= _
           xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
           .Activate
    Finder = ActiveCell.Column
 
    Exit Function
End Function

Hi, cloh.

Finally I've got a time to look close to your code.

• code

   Cells.Find(What:=egg, LookIn:=xlValues, LookAt:= _
             xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
             .Activate

  will fail when no cell will be found. Really you try to invoke Activate method for Range object expected to be returned by Cells.Find method. If no cell found then object is set to Nothing and Nothing.Activate will raise error you are getting.
• you use ActiveCell to determine the result of search but some cell will always be active though not corresponding to search criteria

Here is a sample code for searching within worksheet.

Public Function Finder(DateSelect As Integer) As Integer
 
    Dim egg As String
    Dim colval As Integer
    Dim xlapp As Excel.Application
    Dim xlbook As Excel.Workbook
    Dim xlsheet As Excel.Worksheet
    Dim xlrange As Excel.Range
 
    Set xlapp = CreateObject("Excel.Application")
    With xlapp
        .Visible = True
        'leave Excel app running when the reference (xlapp) destroyed
        .UserControl = True
        Set xlbook = .Workbooks.Add
        Set xlsheet = xlbook.ActiveSheet
    End With
 
    egg = "AmtU_" & DateSelect
    'run find method for the whole worksheet
    'and set range object to cell found
    Set xlrange = xlsheet.Cells.Find(egg)
    If xlrange Is Nothing Then
        'nothing found
        Finder = -1
    Else
        'cell found
        Finder = xlrange.Row
    End If
 
 
    'destroy objects, Excel application stays running
    'bkz xlapp.UserControl = True
    Set xlrange = Nothing
    Set xlsheet = Nothing
    Set xlbook = Nothing
    Set xlapp = Nothing
 
End Function
 

[cloh]

FishVal, what would i do without you? I used the sample code as a template and modified my code to match what I wanted and it now works! I've attached it at the bottom in case anyone has a similar problem. Thank you SO much!

Function Finder(DateSelect As String, sheetindex As Integer) As Integer
    Dim egg As String
    Dim colval As Integer
    Dim xlapp As excel.Application
    Dim xlbook As excel.Workbook
    Dim xlsheet As excel.Worksheet
    Dim xlrange As excel.range
    Dim sheetname As String
    sheetname = "LocID" & sheetindex
    DateSelect_Ref = Format(DateSelect, "MM/DD/YYYY")
 
    Set xlapp = GetObject(, "Excel.Application")
    Set xlbook = ActiveWorkbook
    Set xlsheet = xlbook.Worksheets(sheetindex)
 
    xlsheet.Activate
    Dim title As String
 
    egg = "AmtU_" & DateSelect_Ref
    Set xlrange = xlsheet.Cells.Find(egg)
    If xlrange Is Nothing Then
        Finder = -1
    Else
        Finder = xlrange.Column
    End If
 
    Set xlrange = Nothing
    Set xlsheet = Nothing
    Set xlbook = Nothing
End Function

[FishVal]

Hi, cloh.

I'm glad you appreciate my help and thanks for posting successful solution for those that visit the thread later.

You are welcome.
Good luck and happy coding. :)