1

I would really appreciate some help with the code below. I haven't had much luck trying to make it work the way I want it to. What I'm trying to do is find the value entered in tab 1 cell "K2" in tab 2. If value exist in tab 2 it will select the value and does some code, and then check if another cell exist with the text string. What I can't figure out is creating the loop that will find all cells that have the text string I'm trying to find. Thanks in advance!

Sub test()
Dim SearchRng As range
Dim FindVar As Variant
Dim find As Variant
Dim EndNum As Long
Dim r As range

    Set Final = ActiveWorkbook.Worksheets("Tab 1")
    Set WS = ActiveWorkbook.Worksheets("Tab 2")

    EndNum = WS.range("A1").End(xlDown).row

    Set SearchRng = WS.range("A1:A" & CStr(EndNum))
    Set FindVar = SearchRng.find(range("K2").Value)

    For Each r In FindVar
        If Not FindVar Is Nothing Then
            WS.Activate
            FindVar.Activate
            'my code
            Else
        End If
    Next

End Sub
1

Just in the event you weren't aware, you do not need to .Activate or .Select these ranges - all this does is significantly slow down your code and make your screen do strange things... But this may have been done for testing purposes, which is fine.

Also, you should add Option Explicit to the top of your modules as you never declared your worksheet objects. I did this for you.

You can use the .FindNext method to do what you are asking.

Option Explicit

Sub test()

    Dim ws As Worksheet, Final As Worksheet
    Set Final = ThisWorkbook.Worksheets("Tab 1")
    Set ws = ThisWorkbook.Worksheets("Tab 2")

    Dim EndNum As Long
    EndNum = ws.Cells(Rows.Count, 1).End(xlUp).Row

    Dim sToFind As String, rngFind As Range, addrStart
    sToFind = ws.Range("K2").Value

    With ws.Range("A1:A" & EndNum)
        Set rngFind = .find(sToFind, LookIn:=xlValues)
        If Not rngFind Is Nothing Then
            addrStart = rngFind.Address
            Do
                ws.Activate
                rngFind.Select
                'my code  
                Set rngFind = .FindNext(rngFind)
            Loop While Not rngFind Is Nothing And rngFind.Address <> addrStart
        End If
    End With

End Sub

The reason that your For Each r In FindVar didn't work was that the FindVar was a 1x1 range.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.