code to find, copy and paste until find new, then repeat
Hello all of you genius programmers!!! I need help with a vba macro code.
I'm looking for a code that can do the following:
1 - Search down column A for " / Add New" (that's space slash space Add space New)
2 - For every instance of " / Add New" in Column A, copy that cell and paste it in Column B to the right.
3 - Continue to paste down Column B until another " / Add New" is found in Column A.
4 - When another " / Add New" is found, I need the above actions repeated down the entire column.
If " / Add New" is not searchable because of the slash, I can find/replace it with something like "elephant" or another word that wouldn't be found anywhere else in the column.
I attached a sample of the file I'm working with. Thanks for any help you can give!
Hi
A few small issues:
1) I can't use your sample because it is .xlsx. If you could add one for excel 03 (just .xls) it'd be more helpful.
2) Based upon your direction, here is the code. It seems strange to me that column B will continue to have the same cells posted for multiple rows, but if this code works, then there is no need for a more detailed explanation.
If this isn't what you wanted, explain how it should be different.Code:Public Sub siddharthariver() Dim lngLastRow As Long Dim blnMarker As Boolean lngLastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row blnMarker = False For a = 1 To lngLastRow 'Change 1 to the first row you want to search If InStr(ActiveSheet.Cells(a, 1), " / Add New") > 0 Then Cells(a, 1).Copy blnMarker = True End If If blnMarker = True Then Cells(a, 2).PasteSpecial xlPasteAll End If Next a Application.CutCopyMode = False End Sub
Is your code running too slowly?
Does your workbook or database have a bunch of duplicate pieces of data?
Have a look at this article to learn the best ways to set up your projects.
It will save both time and effort in the long run!
Dave
Seems like your instructions are slightly off. If I understand this correctly, the first time / Add New is encountered, the rest of column A will be copied to column B. Maybe you need some other delimiter to terminate the copy.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks