none
“Application-defined or object-defined error ‘1004’

    Question

  • Hi there
    I am working on a excel worksheet which got two sheet one is Called Result and the other one is called Contracts and VBA
    In sheet Result I got a button for each row part of the vba for button click event is as bellow

    Public Function GetName(lngSiteID As String, strTitle As String) As String

        Dim rngFindTitleRange As Range
        Dim rngFindSiteIDRange As Range
        Dim b As Range
       
       
         Set rngFindSiteIDRange = Sheets("Contacts").Range("B:B").Find(lngSiteID, LookIn:=xlValues, lookat:=xlWhole)
        If Not rngFindSiteIDRange Is Nothing Then
           For Each b In rngFindSiteIDRange.Rows
              Set rngFindTitleRange = b.Range("I:I").Find(strTitle, LookIn:=xlValues, lookat:=xlWhole)
               If Not rngFindTitleRange Is Nothing Then
                  GetName = GetName & rngFindTitleRange.Offset(0, rngFindTitleRange.Column + 2) & ","
              End If
            Next
             Exit Function
        End If
        GetName = "#N/A"

    End Function

    What I am trying to do is
    From sheet result when a user clicked on button  I am calling the sequence of functions and the one of them is GetName
    Where I pass two string  and I wanted to function to do is
    Take this two information and look in sheet Contracts in cell b which matches for lngSiteID 
    If that is  cell b got  the lngSiteID then (it can have more than one row )
    Do a for each to run throw all the line and get the name which matches to the given strTitle
    The example sheet of Contract is as follow


    B            E                 I                       M

    111a      Tony           Manager            Tony.Cook@company.com
    111a     Tom            Manager            Tom.pet@Company.Com
    111a     peter          TeamLead          Peter.j@Company.com


    I am expecting my GetName retun in the if I call
    String Name
    Name = GetName(“111a”, Manager “)
    Name = Tony,Tom

    What I am missed in the above function it throws a “Application-defined or object-defined error ‘1004’ at the line
    Set rngFindTitleRange = b.Range("I:I").Find(strTitle, LookIn:=xlValues, lookat:=xlWhole)
    Appreciate all the help


    Friday, May 18, 2012 10:51 AM

Answers

  • Is the name of the sheet "Contacts" (as in the code) or "Contracts" (as in your description)?

    The Find method only returns the first cell where a value is found, not all cells. You have to use FindNext to find the other occurrences.

    Try this version:

    Public Function GetName(strSiteID As String, strTitle As String) As String
        Dim rngCel As Range
        Dim strAddress As String
        Dim strRetVal As String
    
        With Worksheets("Contracts").Range("B1:B1000")
            Set rngCel = .Find(What:=strSiteID, LookIn:=xlValues, LookAt:=xlWhole)
            If Not rngCel Is Nothing Then
                strAddress = rngCel.Address
                Do
                    If rngCel.Offset(ColumnOffset:=7).Value = strTitle Then
                        strRetVal = strRetVal & ", " & rngCel.Offset(ColumnOffset:=3)
                    End If
                    Set rngCel = .FindNext(After:=rngCel)
                    If rngCel Is Nothing Then Exit Do
                Loop Until rngCel.Address = strAddress
                If strRetVal <> "" Then
                    strRetVal = Mid(strRetVal, 3)
                End If
                GetName = strRetVal
                Exit Function
            End If
        End With
        GetName = "#N/A"
    End Function

    Remark: you can call this function in VBA. It won't work if you use it in a worksheet formula.

    Regards, Hans Vogelaar

    • Marked as answer by Rushdy Najath Friday, May 18, 2012 11:33 AM
    Friday, May 18, 2012 11:25 AM

All replies

  • Is the name of the sheet "Contacts" (as in the code) or "Contracts" (as in your description)?

    The Find method only returns the first cell where a value is found, not all cells. You have to use FindNext to find the other occurrences.

    Try this version:

    Public Function GetName(strSiteID As String, strTitle As String) As String
        Dim rngCel As Range
        Dim strAddress As String
        Dim strRetVal As String
    
        With Worksheets("Contracts").Range("B1:B1000")
            Set rngCel = .Find(What:=strSiteID, LookIn:=xlValues, LookAt:=xlWhole)
            If Not rngCel Is Nothing Then
                strAddress = rngCel.Address
                Do
                    If rngCel.Offset(ColumnOffset:=7).Value = strTitle Then
                        strRetVal = strRetVal & ", " & rngCel.Offset(ColumnOffset:=3)
                    End If
                    Set rngCel = .FindNext(After:=rngCel)
                    If rngCel Is Nothing Then Exit Do
                Loop Until rngCel.Address = strAddress
                If strRetVal <> "" Then
                    strRetVal = Mid(strRetVal, 3)
                End If
                GetName = strRetVal
                Exit Function
            End If
        End With
        GetName = "#N/A"
    End Function

    Remark: you can call this function in VBA. It won't work if you use it in a worksheet formula.

    Regards, Hans Vogelaar

    • Marked as answer by Rushdy Najath Friday, May 18, 2012 11:33 AM
    Friday, May 18, 2012 11:25 AM
  • thanks a lot Hans Vogelaar

    it works

    Friday, May 18, 2012 11:32 AM