Help with a vba finding Function with issues

zoog25

Active Member
Joined
Nov 21, 2011
Messages
418
Hello all,

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

Rich (BB code):
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.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Hi,
try this update to your Function

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top