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

Test1	50
Test2	23
Test3	100
Test4	99
Test5	65

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

Test1	50
Test2	23
	100
	99
	65

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