VB code needed for novice

[Guest]

Hi all,

can anybody help by providing a VB code novice with some code to do the
following...

Search cells E1:E100 for this reference : 07YYDK
when it is found copy the corresponding name from cells C1:C100 to the next
available cell in column AA and then DELETE the reference it has just found
in cells E1:E100.
Then (and the important bit) search the cells again E1:E100 for the same
reference and keep doing this until the reference can no longer be found.

eg

O7YYDK
G23XXF
98HJUY
O7YYDK

from the above list, when the code is run, it will search the list for the
code O7YYDK and when/if found take it and the corresponding name from cells
C1:C100 and paste them into the first available cell in column AA

Then, deletethe code from the original list (cells E1:E100) and perform the
seach again, and loop until the code isn't found,

Hope this makes sense and somebody can help

Thanks

 

[Bernie Deitrick]

Anthony,

Sub AnthonyFindClear()

Dim myCell As Range

Set myCell = Range("E1:E100").Find(What:="07YYDK")
While Not myCell Is Nothing
myCell.Offset(0, -2).Copy Range("AA65536").End(xlUp)(2)
myCell.Clear
Set myCell = Range("E1:E100").FindNext
Wend
End Sub

HTH,
Bernie
MS Excel MVP

 

[Guest]

Bernie

Many thanks for that, I'll give it a try..just one other simple question...

If I wanted to run another 3 macros at the end of this, how do I??

Di I add a live of code like...

Call Formula_two (being the name of the second macro)
Call Formula_Three (being the third)

etc etc

if so do I place this at the end of your code, ie

Set myCell = Range("E1:E100").FindNext
Wend
Call Formula_Two
Call Formula_Three
End Sub

thanks again

 

[Bernie Deitrick]

Anthony,

Yes, though you don't really need the Call part....

HTH,
Bernie
MS Excel MVP

 

[Guest]

Bernie,

I Have just tried ur code

Sub AnthonyFindClear()
Dim myCell As Range
Set myCell = Range("E1:E100").Find(What:="07YYDK")
While Not myCell Is Nothing
myCell.Offset(0, -2).Copy Range("AA65536").End(xlUp)(2)
myCell.Clear
Set myCell = Range("E1:E100").FindNext
Wend
End Sub

when attached to a button and I get a MICROSOFT VISUAL BASIC ERROR 400 and
nothing happens or If I play the code (using F8) in the VB editor it gets to
the

myCell.Offset(0, -2).Copy Range("AA65536").End(xlUp)(2)

part of the code then an error code of
Run-time error 1004
Application-defined or object-defined error


any ideas????

 

[Guest]

Bernie,

Please ignore my previous post - I must have had a typo.....the code works
just fine and thanks again for your help

 

[Bernie Deitrick]

Anthony,

Make sure that the TakeFocusOnClick property of the button is set to False.

HTH,
Bernie
MS Excel MVP