Modifying code for copying and pasting between sheets

[jrpettit]

Hi, and welcome to the forum.

Please take a few moments to review the rules and guidance notes, particularly the requirement to make sure VBA code is placed between code tags, which makes it more readable. I've edited it for you on this occasion.

The answer to your question is to use the VBA instruction Instr in your code. i.e.

If Instr(Range("A" & CStr(LSearchRow))) = ";" Then

The default result for a Range instruction is the Value of the range addresses, hence there's no need for .Value

You will also speed up your macro if you do away with all the .Select instructions. There's no need for them. Just address the object directly and add the appropriate instruction. So instead of

Row(blah blah).Select
Selection.Copy

'Just use

Row(blah blah).Copy

Hello-

I am working on a sheet where I want to copy and paste all rows within a column that contain the value ";" into another sheet. This code works if the cell value is equal to ";" alone but not if it's mixed with other characters. (ex: P330;P440). I think this is relatively simple to modify, but I am new to macros and can't figure it out. Here is the code (copied from another source and modified).........

Sub SearchForString()

    Dim LSearchRow As Integer
    Dim LCopyToRow As Integer

    On Error GoTo Err_Execute

    'Start search in row 2
    LSearchRow = 2

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

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

        'If value in column A equals ";", copy entire row to Sheet2
        If Range("A" & CStr(LSearchRow)).Value = ";" Then

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

            'Paste row into Sheet2 in next row
            Sheets("Sheet2").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

Any help would be greatly appreciated. Thanks!

john

[jrpettit]

Thanks a lot for the suggestion. Apologies about the formatting. Seems like it's still not working though after I modified it. It gives me an error message that reads "Compile error: Syntax error." Any ideas what's wrong now? Here is the updated code....

Sub SearchForString()

    Dim LSearchRow As Integer
    Dim LCopyToRow As Integer

    On Error GoTo Err_Execute

    'Start search in row 2
    LSearchRow = 2

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

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

        'If value in column A equals ";", copy entire row to Sheet2
        If Instr(Range("A" & CStr(LSearchRow))) = ";" Then

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

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

            'Move counter to next row
            LCopyToRow = LCopyToRow + 1

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

        End If

        LSearchRow = LSearchRow + 1

    Wend

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

    MsgBox "All matching data has been copied."

    Exit Sub

Err_Execute:
    MsgBox "An error occurred."

End Sub

Thanks, john

[jrpettit]

To clarify further, this line comes up highlighted in red.........

If Instr(Range("A" & CStr(LSearchRow))) = ";" Then