Return Cell Value

Question

My macro will take the contents of a box and search on another sheet for a cell or cells containing the value.

It will tell me the cell that the value is within, however I am looking for the value returned to be the value in another cell.

I.e. as outlined below

Column A = Name

Column B = Tel

Column C = Approval

The search is completed on C however the result returned should be the contents of Column A.

I am new to VBA and learning as I am going along

Thanks in advance.

Answer 1

Hi Gord

Yes the search is completed in Column K, the result I want to produce will be in Column A

Sorry I said A B C to simplify but I obviously confused the situation

Answer 2

Can we get something confirmed?

When you use the letter K do you mean Column K?

Your original post mentioned columns A, B and C

In my code the lowercase c is not a column, c is a cell

Gord

Answer 3

Hi Gord

I have adjusted the code to match my need, I was obviously being to generic

Sub TestFindManufacturer()

whatname = InputBox("Insert Name Here")

Sheets("Network").Select

    With ActiveSheet.Range("A:F")

    Set k = .Find(whatname, LookIn:=xlValues, lookat:=xlPart)

        If Not k Is Nothing Then

            firstAddress = k.Address

            Do

                MsgBox k.Offset(0, -2).Address

                Set k = .FindNext(k)

            Loop While Not k Is Nothing And k.Address <> firstAddress

        End If

    End With

  If k Is Nothing Then

           MsgBox ("name not found")

      End If

End Sub

I know in K there is definitely 'Citroen' however the response given is name not found?

Have I adjusted the code to the extent it will not work?

The code I am currently using is below

Sub FindManufacturer()

 Dim SearchFor As Variant

 Dim rCell As Excel.Range

 Dim firstAddress As String

 Dim strMessage As String

 SearchFor = Range("J14")

 With Worksheets(2).Cells

  Set rCell = .Find(SearchFor, LookIn:=xlValues)

  If Not rCell Is Nothing Then

    firstAddress = rCell.Address

    strMessage = rCell.Address(external:=True)

    Do

      Set rCell = .FindNext(rCell)

      If rCell Is Nothing Or rCell.Address = firstAddress Then Exit Do

      strMessage = strMessage & vbCr & rCell.Address(external:=True)

    Loop

  End If

 End With

 MsgBox strMessage

End Sub

But this will only tell me the cell location and not the Name of the repairer (COLOUMN A) or repairers that has the matching manufacturer found in coloumn K

Answer 4

If you're sure it will always be C where the value is found. . . .

Sub findthings()

whatname = InputBox("Enter a Name")

    With ActiveSheet.Range("A:F")

    Set c = .Find(whatname, LookIn:=xlValues, lookat:=xlPart)

        If Not c Is Nothing Then

            FirstAddress = c.Address

            Do

                MsgBox c.Offset(0, -2).Address

                Set c = .FindNext(c)

            Loop While Not c Is Nothing And c.Address <> FirstAddress

        End If

    End With

  If c Is Nothing Then

           MsgBox ("name not found")

      End If

End Sub

Gord