Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find in column and copy
I am new to macros. I have managed to do most in the project but this one has
stumped me. I want to search Col. "A" for a text string. When found, cut the cell contents to the right of the text from Col. "B" and paste it up one line and into Col. "C". Then continue searching Col. "A" for more instances of the text string. Richard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find in column and copy
Give this a wirl...
Sub FindAndMove() Dim wksToSearch As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim strFirst As String Dim strSearchText As String strSearchText = "this" 'Change This Set wksToSearch = Sheets("Sheet1") 'Change This Set rngToSearch = wksToSearch.Columns("A") Set rngFound = rngToSearch.Find(What:=strSearchText, _ LookAt:=xlWhole, _ LookIn:=xlFormulas, _ MatchCase:=False) If rngFound Is Nothing Then MsgBox "Sorry. Nothing was found" Else strFirst = rngFound.Address Do rngFound.Offset(-1, 2) = rngFound.Offset(0, 1) rngFound.Offset(0, 1).ClearContents Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = strFirst End If End Sub -- HTH... Jim Thomlinson "Syscon" wrote: I am new to macros. I have managed to do most in the project but this one has stumped me. I want to search Col. "A" for a text string. When found, cut the cell contents to the right of the text from Col. "B" and paste it up one line and into Col. "C". Then continue searching Col. "A" for more instances of the text string. Richard |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find in column and copy
Sub test()
Range("A2").Select 'Set for start range Do Until ActiveCell = "" If ActiveCell = "XXX" Then 'Test to find vText = ActiveCell ActiveCell.Offset(-1, 2) = vText End If ActiveCell.Offset(1, 0).Select Loop End Sub Rob Edwards Always look on the bright side of life! *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find in column and copy
Thanks Rob,
This is a bit shorter than Jims, but yours copies the "Text" in Col. "A" and not the data from Col. "B" - any ideas why? Richard "Rob Edwards" wrote: Sub test() Range("A2").Select 'Set for start range Do Until ActiveCell = "" If ActiveCell = "XXX" Then 'Test to find vText = ActiveCell ActiveCell.Offset(-1, 2) = vText End If ActiveCell.Offset(1, 0).Select Loop End Sub Rob Edwards Always look on the bright side of life! *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find in column and copy
Hi Syson,
'--------------- This is a bit shorter than Jims, but yours copies the "Text" in Col. "A" and not the data from Col. "B" - any ideas why? '--------------- Brevity of code and efficiency are not necessariliy synonymous. Did you try Jim's suggested code. As far as Rob's code is concened, try changing: vText = ActiveCell to vText = ActiveCell.offse(0,1).Value --- Regards, Norman |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find in column and copy
Hi Syson,
vText = ActiveCell.offse(0,1).Value was intended as: vText = ActiveCell.Offset(0,1).Value --- Regards, Norman |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find in column and copy
Thanks everyone. Both versions work fine.
Very grateful for everyone help. Richard "Norman Jones" wrote: Hi Syson, vText = ActiveCell.offse(0,1).Value was intended as: vText = ActiveCell.Offset(0,1).Value --- Regards, Norman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find cell and copy value to column to the right | Excel Discussion (Misc queries) | |||
Find specific column and copy row. | Excel Programming | |||
Find specific column titles and copy the column to new workboo | Excel Programming | |||
Find specific column titles and copy the column to new workboo | Excel Programming | |||
find value and copy column to new spreadsheet | Excel Programming |