Find / FindNex - VBA Problem

[Roque]

Hi,
I have a macro that need to:
1) find all occurences in spreadsheet-1 (every occurrence is a set of 3 cells, for example B1+C1+D1
2) move this data to spreadsheet-2
3) clear this occurrence in spreadsheet-1

This is the code I tried for these steps:

Do
sheet1.activate
Set OccSearch = RangeSheet1.Find(What:=Occ, lookat:=xlWhole)
Item = Range("A" & OccSearch.Row)
OccSearch.Resize(1, 3).Select
Selection.Copy
sheet2.Activate
RangeSheet2.Find(What:=Item, lookat:=xlWhole).End(xlToRight).Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues
sheet1.Activate
If OccSearch.Offset(0, 3) = "" Then
Selection.ClearContents
Else
InvSearch.Offset(0, 3).Activate
Selection.Resize(1, ActiveCell.End(xlToRight).Column).Copy
Selection.Offset(0, -3).PasteSpecial Paste:=xlPasteValues
End If
Set OccSearch = rangesheet1.FindNext(OccSearch)
Loop Until OccSearch Is Nothing

The problem I noticed is that when I run the line Selection.ClearContents it changes the value of the variable OccSearch
Is there any way to avoid this?

Thanks!

 

[Roque]

Is there any way to avoid the variable OccSearch is changed?

 

[excelfan92]

I'm not that expert in VBA, but... I can't see any problem...
Have you tried to rename the variable?