Help with .Find intermittently not working

gordsky

New Member
Joined
Jun 2, 2016
Messages
13
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

VBA Code:
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,194
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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.
 
Solution

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,349
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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...
VBA Code:
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.
 

Forum statistics

Threads
1,141,400
Messages
5,706,217
Members
421,433
Latest member
yash0468

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top