Hi,

First let me say that this is my first attempt at programming in Excel and any programming experience I have is in RPG so it’s not really helping me through this.

To give a little context: The team I work with have finally become fed up with the shear number of business cards from external companies we have floating around and we want to put together a quick and dirty repository for the contact details. Along with this I was going to include external company’s information so we can quickly see what services/software they provide to use.

What I’m looking at is sheet containing individual’s information, one to hold company info, a main sheet to permit searches on people with the results displayed on this sheet and an additional sheet for searching on a company displaying the company details and a list of contacts (obtained from the sheet that holds individuals data) we have at that company.

Well, I thought ‘I have some programming knowledge, won’t take long’ – seems my knowledge and Google have both failed me….


I’m asking for help with the initial search for the individuals as I think I can use and modify code for that to other areas of the spreadsheet. At the moment the code I have finds the record and enters only the first name into the specific results cell, I need to retrieve all the individuals information from the search (each individual has a single row with cells A-N used) and enter them into the appropriate cells and I also would like to accommodate multiple search results.

I’d like to change the ‘for’ loop in the code so it doesn’t search each sheet just whichever is specified in the code.

Below is the embarrassing attempt I have made:
Private Sub Forename_Search_Click()

Dim ws As Worksheet, myvar As String, val1 As Range
Dim cnt As Integer
    cnt = 0
    myvar = Forename
    If myvar = "" Then Exit Sub
    For Each ws In ThisWorkbook.Worksheets
        Set val1 = ws.Cells.Find(What:=myvar, LookIn:=xlValues, _
        lookat:=xlWhole, MatchCase:=False)
        If Not val1 Is Nothing Then
            cnt = cnt + 1
            Forename = ""
            Worksheets("Menu").Range("C11").Value = val1
        End If
    Next ws
If cnt = 0 Then MsgBox "No Matches Found"

End Sub