Previous Topic Next Topic

Search column for text "X" then copy to

Closed
shannon - Feb 29, 2012 at 09:41 AM
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
 - Mar 6, 2012 at 10:04 AM
Hello,
I would like to have a macro to search a column B for text "X". Wherever text "X" is found in that column, I want to copy text "X" and the corresonding cell in that row in column A. I want to transfer each case to a different worksheet.

Column A Column B
Question 1 A
Question 2 A
Question 3 D
Question 4 F
Question 5 X
Question 6 E
Question 7 W
Question 8 X

Result on different worksheet
X Question 5
X Question 8

Thanks


1 reply

Answer 1 / 1
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
 509
Mar 6, 2012 at 10:04 AM
Hi Shannon,

Try this code: 
Sub test()
Dim lRow, lRow2 As Integer
lRow = Sheets("Main").Range("B" & Rows.Count).End(xlUp).Row
    For Each cell In Sheets("Main").Range("B2:B" & lRow)
        If cell.Value = "X" Then
lRow2 = Sheets("Dest").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
cell.Copy Destination:=Sheets("Dest").Range("A" & lRow2)
cell.Offset(0, -1).Copy Destination:=Sheets("Dest").Range("B" & lRow2)
        End If
    Next cell
End Sub

Please adjust sheet names ("Main" and "Dest") to match yours.

Best regards,
Trowa
0