How to find a certain string and then take that string and search it in other cell in vba?

Question

How can I find a particular String and then copy that string, search for it in a different cell then if I find the correct string on the other cell, go to the corresponding cell and copy and paste it into another cell? I keep on getting an error while I am using Instr then another Instr. Any suggestion please anyone. If you see the attached then basically what i am trying to do is First find H which is in michale, then copy michale and search for it in A column, once i find it in A column i go to corresponding column and copy Pick-me* paste it any where in sheet.

     Sub ex_find()

       Dim ws As Worksheet, m As String, lastrow As Long
          Set ws = ActiveWorkbook.Worksheets("Sheet3")
           lastrow = ws.UsedRange.Rows.Count + 1
              For i = 2 To lastrow
                m = ws.Cells(i, 5)
                  If InStr(ws.Cells(i, 5), "h") > 0 Then
                    InStr(ws.Cells(i, 1), "m") = 0

                  End If

             Next 
    End Sub

John Muggins John Muggins · Accepted Answer · 2017-05-30T20:50:14

Your question confuses me a little. I think you want to search for an "h" in values of column "F" and when found search column "A" for the value of found cell in column "F". Then you want to do something with a "corresponding cell"

Sub ex_find()

    Dim ws As Worksheet, m As String, lastrow As Long, myArray() As Variant
    Set ws = ActiveWorkbook.Worksheets("Sheet3")
    lastrow = ws.UsedRange.Rows.Count + 1

    For i = 1 To lastrow
        If InStr(1, ws.Range("F" & i).Value, "h") <> 0 Then
            With ws.Range("a1:a" & lastrow)
                Set c = .Find(ws.Range("F" & i).Value, LookIn:=xlValues)
                If Not c Is Nothing Then
                    'firstAddress = c.Address
                    'Do
                    'This is where you put your code to change "corresponding cell"



                    'Loop While Not c Is Nothing And c.Address <> firstAddress 

                End If
            End With
        End If
    Next i

End Sub

How to find a certain string and then take that string and search it in other cell in vba?

1 Answers