Help with a vba finding Function with issues

[zoog25]

Hello all,

I'm trying to draft my own search function from a list found on one sheet.

Function Contactr(target As Long)


Set NOCws = ThisWorkbook.Worksheets("NOC")
Set Conws = ThisWorkbook.Worksheets("Contact")


lrow = Conws.Cells(Rows.Count, 3).End(xlUp).Row


With Conws.Range("C5:C" & lrow)
Set ConR = NOCws.Find(Cells(target, "I"), LookIn:=xlValues)
        If Not ConR Is Nothing Then
            'Contactr = ConR.Row
            Contactr = .Cells("G", ConR.Row).Value & ", " & .Cells("H", ConR.Row).Value & ", " & .Cells("I", ConR.Row).Value & " " & .Cells("J", ConR.Row).Value
        End If
End With


End Function

The Section in read is giving me issues. The thing is that the item be searched for is a name of a developer found in column C of the Contact sheet. If the function finds the developers name, then i want it to extra the address of the developer found in columns G, H, I, & J. Can you please tell me if i'm using the right method, if not how can i fix this so it will work in the extraction.

 

[dmt32]

Hi,
try this update to your Function

Function Contactr(ByVal Search As String) As String
    Dim Conws As Worksheet
    Dim ConR As Range


    Set Conws = ThisWorkbook.Worksheets("Contact")


    Set ConR = Conws.Columns(3).Find(Search, LookIn:=xlValues, lookat:=xlWhole)


    If Not ConR Is Nothing Then
        With ConR
            Contactr = .Offset(, 4).Value & ", " & _
                       .Offset(, 5).Value & ", " & _
                       .Offset(, 6).Value & " " & _
                       .Offset(, 7).Value
        End With
    End If
End Function

Pass the name of the developer you are searching for as a string to the function.

Dave