| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB code needed for novice
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB code needed for novice
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 "Anthony" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB code needed for novice
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" wrote: 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 "Anthony" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB code needed for novice
Anthony,
Yes, though you don't really need the Call part.... HTH, Bernie MS Excel MVP "Anthony" wrote in message ... 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" wrote: 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 "Anthony" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB code needed for novice
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???? "Bernie Deitrick" wrote: Anthony, Yes, though you don't really need the Call part.... HTH, Bernie MS Excel MVP "Anthony" wrote in message ... 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" wrote: 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 "Anthony" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB code needed for novice
Anthony,
Make sure that the TakeFocusOnClick property of the button is set to False. HTH, Bernie MS Excel MVP "Anthony" wrote in message ... 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???? "Bernie Deitrick" wrote: Anthony, Yes, though you don't really need the Call part.... HTH, Bernie MS Excel MVP "Anthony" wrote in message ... 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" wrote: 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 "Anthony" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB code needed for novice
Bernie,
Please ignore my previous post - I must have had a typo.....the code works just fine and thanks again for your help "Anthony" wrote: 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???? "Bernie Deitrick" wrote: Anthony, Yes, though you don't really need the Call part.... HTH, Bernie MS Excel MVP "Anthony" wrote in message ... 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" wrote: 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 "Anthony" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| A macro code question from a novice | Excel Discussion (Misc queries) | |||
| VB code needed as this is way beyond a novice like me! | Excel Programming | |||
| Code Help Needed | Excel Programming | |||
| Code Fix Needed | Excel Programming | |||
| VBA code Help needed | Excel Programming | |||
Hybrid Mode
