Help with .Find intermittently not working

[gordsky]

Hi,

I have the below code attached to a button on a worksheet. Most of the time it will work fine however occasionally it will not run correctly even when nothing on the sheet has changed. this was the case today as it worked find last nigh but upon running this morning it failed to find the data even though nothing had changed on the sheet.

Is anyone able to advise or suggest why this may be happening. Im fairly certain it has something to do with the .Find as no error messages are returned by the code, it simply fails to find the search data within the specified range and seems to see the "Ans" reply as being Nothing even though it definitely there. Ive looked at formatting etc and the search data is exactly the same as the find data so no idea why it doesn't find it occasionally.

Ive also tried expanding the bits of the code to exact locations (rather than variable) with no joy

Private Sub CommandButton1_Click() 'Creates the Default
Dim i As Long
Dim ws1, ws2 As Worksheet
Dim Ans, Rng1, Rng2, r1, r2, r3 As Range
Dim MySheet As String

Set ws1 = Sheets("AdHoc")
MySheet = ws1.Range("N13")
Set ws2 = Sheets(MySheet)
Set Rng1 = ws1.Range("C2:C53")
Set Rng2 = ws2.Range("C2:C53")
i = 2

Application.ScreenUpdating = False

   For Each x In Rng1
          
      Set Ans = Rng2.Find(what:=ws1.Range("C" & i).Text)
                        
         If Not Ans Is Nothing Then
            r1 = Ans.Row
                
            If Rng2.Find(ws1.Range("C" & i).Text) = x Then
                    
              Set r2 = ws1.Range("D" & i & ":J" & i)
                 Set r3 = ws2.Range("D" & r1 & ":J" & r1)
                    r3.Copy r2
                
            Else
            End If
                
         Else
         End If
            
   i = i + 1
   Next x
   End sub

 

[MARK858]

Find retains a memory of the last time it was used with most Parameters and I would guess that is your issue. It is rarely a good idea to only specify the search for value and leave all the other parameters unspecified.
You won't get an error message as the code tests if the value is found.

 

[Rick Rothstein]

I think Mark has the answer to your problem. Just to clarify... it does not matter whether you use the Find dialog box or call the Find method of a range, if any of several parameters (for example, match case or match entire cell content) are set... those parameters remain in effect from call to call until changed.

On another note, unlike most other programming languages, VB requires all variable to be declared as to their data type and, if you don't, they default to Variants. So for these two Dim statements...

Dim ws1, ws2 As Worksheet
Dim Ans, Rng1, Rng2, r1, r2, r3 As Range

only ws2 is declared as a worksheet and only r3 is declared as a range... all of the other variables are declared as Variants.