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

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

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 ***

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 ***

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

Find in column and copy
 

Hi Syson,

vText = ActiveCell.offse(0,1).Value

was intended as:

vText = ActiveCell.Offset(0,1).Value


---
Regards,
Norman

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