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