What is wrong with this code? .Find , VBA

0
votes

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
3
excelvbafind
You need to post more code. Where is X Dim'ed?? - Gary's Student
on top of the sheet as a global variable.. - user2703472
Is X a Range or a value?? - Gary's Student
As a string....because it can be anything..number ..or text - user2703472
O.K.....so each time Find2() gets called, its searching always starts to the top rather than at the point it found the previous X?? - Gary's Student

3 Answers

1
votes

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

0
votes

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
0
votes

Use

private Function Find2()
end function

insted of sub