code not working correctly

[redspanna]

Hi all

This code was suggested to me many moons ago...

It searches through column A to match a text string. Once found it will then copy the adjacent number value into certain column - next available line - then it is meant to delete the found value from column B and matching text string in column A

Set Mycell = Sheets("Sorted Data").Range("A:A").Find(What:="*Test1*") 
While Not Mycell Is Nothing
Mycell.Offset(0, 1).Copy Range("C65536").End(xlUp)(2)
Mycell.Clear
Set Mycell = Range("A:A").FindNext
Wend


Set Mycell = Sheets("Sorted Data").Range("A:A").Find(What:="*Test2*") 
While Not Mycell Is Nothing
Mycell.Offset(0, 1).Copy Range("D65536").End(xlUp)(2)
Mycell.Clear
Set Mycell = Range("A:A").FindNext
Wend


Set Mycell = Sheets("Sorted Data").Range("A:A").Find(What:="*Test3*") 
While Not Mycell Is Nothing
Mycell.Offset(0, 1).Copy Range("E65536").End(xlUp)(2)
Mycell.Clear
Set Mycell = Range("A:A").FindNext
Wend

Set Mycell = Sheets("Sorted Data").Range("A:A").Find(What:="*Test4*") 
While Not Mycell Is Nothing
Mycell.Offset(0, 1).Copy Range("F65536").End(xlUp)(2)
Mycell.Clear
Set Mycell = Range("A:A").FindNext
Wend


Set Mycell = Sheets("Sorted Data").Range("A:A").Find(What:="*Test5*") 
While Not Mycell Is Nothing
Mycell.Offset(0, 1).Copy Range("L65536").End(xlUp)(2)
Mycell.Clear
Set Mycell = Range("A:A").FindNext
Wend

weird problem is that code works and does what t should, but doesn't always delete the number value from column B

example


[table="width: 150, class: grid"]
[tr]
[td]Test1[/td]
[td]50[/td]
[/tr]
[tr]
[td]Test2[/td]
[td]23[/td]
[/tr]
[tr]
[td]Test3[/td]
[td]100[/td]
[/tr]
[tr]
[td]Test4[/td]
[td]99[/td]
[/tr]
[tr]
[td]Test5[/td]
[td]65[/td]
[/tr]
[/table]

once code runs, some expected data is left behind when should be deleted....


[table="width: 150, class: grid"]
[tr]
[td]Test1[/td]
[td]50[/td]
[/tr]
[tr]
[td]Test2[/td]
[td]23[/td]
[/tr]
[tr]
[td][/td]
[td]100[/td]
[/tr]
[tr]
[td][/td]
[td]99[/td]
[/tr]
[tr]
[td][/td]
[td]65[/td]
[/tr]
[/table]


any explanation, or suggest better way of working code?

Many thanks in advance

 

[Fluff]

As this is effectively a duplicate of https://www.mrexcel.com/forum/excel-questions/1097431-change-mycell-clear-code.html

I am closing this thread