BMurphyNSTG
New Member
- Joined
- Mar 8, 2017
- Messages
- 3
Hello,
I am writing a macro in Excel VBA to search for a string (stored in column A) in column B and then paste the row where the match occurs in a new sheet. I started working off of this tutorial, which had almost everything I needed. The difference is that the value I'm searching for in column B keeps changing in column A.
I think I am close, but I keep getting the following error: "Run-time error '1400': Application-defined or object-defined error". I can tell that the code starts working because the correct row is highlighted and my view switches to Sheet2 from Sheet1, but it stops there.
Does anyone know what I need to change in my code to successfully paste the row into Sheet2?
Here is the table I'm working with:
<tbody>
</tbody>
I am writing a macro in Excel VBA to search for a string (stored in column A) in column B and then paste the row where the match occurs in a new sheet. I started working off of this tutorial, which had almost everything I needed. The difference is that the value I'm searching for in column B keeps changing in column A.
I think I am close, but I keep getting the following error: "Run-time error '1400': Application-defined or object-defined error". I can tell that the code starts working because the correct row is highlighted and my view switches to Sheet2 from Sheet1, but it stops there.
Does anyone know what I need to change in my code to successfully paste the row into Sheet2?
Code:
Sub SearchForString()
Dim ASearchRow As Integer
Dim BSearchRow As Integer
Dim CopyToRow As Integer
' On Error GoTo Err_Execute
'Start search in row 2
ASearchRow = 2
BSearchRow = 2
'Start copying data to row 2 in Sheet2 (row counter variable)
CopyToRow = 2
'Dim myWS As Worksheet
'Set myWS = Worksheets("Sheet1")
'Sheets("Sheet1").Select
While Len(Range("A" & CStr(ASearchRow)).Value) > 0
'If value in column B = (what I'm looking for), copy entire row to Sheet2
If Range("A" & CStr(ASearchRow)).Value = Range("B" & CStr(BSearchRow)).Value Then
'Select row in Sheet1 to copy
Rows(CStr(BSearchRow) & ":" & CStr(BSearchRow)).Select
Selection.Copy
'Paste row into Sheet2 in next row
Sheets("Sheet2").Select
Rows(CStr(CopyToRow) & ":" & CStr(CopyToRow)).Select
ActiveSheet.Paste
'Move counter to next row
CopyToRow = CopyToRow + 1
'Go back to Sheet1 to continue searching
Sheets("Sheet1").Select
Else
'If the two cells in the same row do not match, the cell in column B will continue looking in the rest of column B
BSearchRow = BSearchRow + 1
End If
'ASearchRow = ASearchRow + 1
Wend
'Resetting the B counter to the beginning
BSearchRow = 2
'Position on cell A3
Application.CutCopyMode = False
Range("A2").Select
MsgBox "All matching data has been copied."
Exit Sub
Err_Execute:
MsgBox "An error occurred."
End Sub
Here is the table I'm working with:
column1 | dataset | |||
two2 | 7 | a1 | a2 | a3 |
three3 | three3 | b1 | b2 | b3 |
four4 | 5555fivefivefivefive | c1 | c2 | c3 |
34thirtyfour | five5 | d1 | d2 | d3 |
five5 | 34 | e1 | e2 | e3 |
21245 | two2 | f1 | f2 | f3 |
6677 | five5 | g1 | g2 | g3 |
63 | 21245 | h1 | h2 | h3 |
73 | 6677 | j1 | j2 | j3 |
86 | 63 | k1 | k2 | k3 |
73 | l1 | l2 | l3 | |
86 | m1 | m2 | m3 | |
four4 | n1 | n2 | n3 | |
1 | o1 | o2 | o3 | |
1 | p1 | p2 | p3 | |
6 | q1 | q2 | q3 |
<tbody>
</tbody>