"An Error Occured" with a copy paste macro and recommendations

[raramachine]

Hello, I'm quite new at this, so please forgive any lack of knowledge.

I'm trying to get a macro to search for a term in a column (in this case, Camacho Services and column D) and when found, copy the row that has that data to another sheet. As I am a newbie, I modified the code from elsewhere, though I think I have changed the appropriate variables.

Whenever I run the code though, I get the error text. I've tried several permutations, but it there is still something going wrong.

The code is:


Sub SearchForString()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

'Start search in row 4
LSearchRow = 4

'Start copying data to row 2 in Sheet4 (row counter variable)
LCopyToRow = 2

While Len(Range("A" & CStr(LSearchRow)).Value) > 0

'If value in column D = "Camacho Services", copy entire row to Sheet4
If Range("D" & CStr(LSearchRow)).Value = "Camacho Services" Then

'Select row in Sheet1 to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into Sheet4 in next row
Sheets("Sheet4").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Sheet1 to continue searching
Sheets("Sheet1").Select

End If

LSearchRow = LSearchRow + 1

Wend

'Position on cell A3
Application.CutCopyMode = False
Range("A3").Select

MsgBox "All matching data has been copied."

Exit Sub

Err_Execute:
MsgBox "An error occurred."


End Sub

Can anyone advise? Did I fail to change some variables pertinent to my desired search term or column? Did I accidentally mess something else up?


Also, I hope to become much better at this. Does anyone have tutorial recommendations?
Thanks so much.

 

[L. Howard]

Try this.

Hoard

Option Explicit

Sub my_Instr_Copy()
Dim LRow As Long
Dim rngC As Range

With Sheets("Sheet1")
   LRow = .Cells(.Rows.Count, "A").End(xlUp).Row

  For Each rngC In .Range("A4:A" & LRow)

    If InStr(rngC, "Camacho Services") > 0 Then
      rngC.EntireRow.Copy Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp)(2)
    End If

  Next
End With

End Sub

 

[raramachine]

Thank you very much for your response. Unfortunately, I am still doing something wrong/ having problems.

When I try to run it, it says:

Runtime error '9':

Subscript out of range.

 

[L. Howard]

Make sure the sheet names of your workbook match the sheet names in the code.

If that is not the problem, post a link to an example workbook. Use one of the link utilities, I use Drop Box, but there are others.

Re state what you want to happen and where, referring to the workbook, sheets/columns/rows... etc.

Howard

 

[L. Howard]

Here is a sample workbook, see if this is doing what you want.

Click the small button, check sheet 4.

Howard


https://www.dropbox.com/s/74rjbwtkee836p4/Book1 INSTR Drop Box.xlsm?dl=0

 

[raramachine]

Yes! This is exactly it!

But I need it to be column "D" while this macro seems only to do column "A".

Will changing all instances of "A" to "D" do it? Or should some "A"s Be left alone?

Thank you!

 

[L. Howard]

Try this, where it looks for the "Camacho Services" in column D and copies to sheet 4 column A. If you want to copy to column D on sheet 4, change the red A to D.

Howard

Option Explicit

Sub my_Instr_Copy()
Dim LRow As Long, LCol As Long
Dim rngC As Range

With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, "D").End(xlUp).Row

For Each rngC In .Range("D4:D" & LRow)

   LCol = Cells.Find(What:="*", After:=[a1], _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious).Column

   If InStr(rngC, "Camacho Services") > 0 Then
     rngC.Resize(1, LCol).Copy Sheets("Sheet4").Range("[COLOR="#FF0000"]A[/COLOR]" & Rows.Count).End(xlUp)(2)
   End If

Next
End With
End Sub

 

[raramachine]

Yes! It's perfect! Thank you so much!

We have hundreds of these entries, you've saved me the trouble of going through them one by one and it still being possible that I missed one!

Thanks again!

 

[L. Howard]

Okay, glad it is working for you.

With hundred's of rows, does the code take a long time to run?

Perhaps something like this will be a faster way to identify the rows that have "a word of concern".

Where this returns TRUE in the row searched, in a column of choice, column B in this code.

Howard

Option Explicit

Sub Formula_Way()

  Dim lRowCount As Long
  lRowCount = Cells(Rows.Count, "D").End(xlUp).Row
  
  With Range("B4").Resize(lRowCount)
    .Formula = "=IF(ISNUMBER(SEARCH(""Camacho Services"",D4))=FALSE,"""",TRUE)"
  End With
  
End Sub

 

[raramachine]

It didn't take too long, fortunately!

But as we add more to the excel table over time, it is possible it might one day.

I will certainly keep this other code handy. But for now, the appropriate rows are in a new document and my problem is solved!

Thanks so much again!