CODEWITHSUNDEEP
excelvbafind
user2703472
user2703472

Reputation: 45

What is wrong with this code? .Find , VBA

I have two subs, First sub "Find" finds all the values in column G , range (G1:G10) , there i have three values so it loops 3 times and gives me the value.

Now i am calling a second sub "Find2" to find that value in column A, range (A1:A10). the problem is it runs only once.. it is not looping three times to get 3 values. Any idea ?. why it is not working.

Sub Find()

Set shtSheet1 = Sheets("Sheet1")
With shtSheet1.Range("G1:G10")
    Set V = .find("*", LookIn:=xlValues)
    If Not V Is Nothing Then
        FirstAddress = V.Address

        Do
        X = V
        Call Find2
        Set V = .FindNext(V)

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

    End If
End With

End Sub

Second sub 

Public Sub Find2()

Set shtSheet1 = Sheets("Sheet1")
Set shtSheet2 = Sheets("Sheet2")

    With shtSheet1.Range("A1:A10")
        Set C = .find(X, LookIn:=xlValues)
        If Not C Is Nothing Then
        MsgBox X
        End If
    End With

End Sub

Upvotes: 0

Views: 283

Answers (3)

Running Forward
Running Forward

Reputation: 90

Use

private Function Find2()
end function

insted of sub

Upvotes: 0

DaveU
DaveU

Reputation: 1082

I agree with Gary, there's some interference going on. Instead of using Find() in your subroutine, see if this will work for you.

Public Sub Find2()
    Dim shtSheet1 As Worksheet
    Dim C As Range

    Set shtSheet1 = Sheets("Sheet1")
    Set C = shtSheet1.Range("A1:A10")

    If Not IsError(Application.Match(X, C, 0)) Then
        MsgBox X
    End If

End Sub

Upvotes: 0

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96791

I think that using .Find() in the subroutine is interfering with the .FindNext() in the main routine.

Upvotes: 1

Related Questions