Excel VBA macros does not find matches

Question

I have a macro set-up in a Excel workbook; its objective is: 1. In Sheet1, to search a table for a specific value in a column. 2. If that value is found, then it must copy the entire line to Sheet2.

Sub procurarnegociacion()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

'Start search in row 5
LSearchRow = 5

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

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

    'If value in column W = 1, copy entire row to Sheet2
    If Range("W" & CStr(LSearchRow)).Value = "1" 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 data copied."

Exit Sub

Err_Execute:
MsgBox "Error ocurred."

End Sub

But here is the problem: imagine I search for value "5" and I only have 1-5 values in that column W. The macro only works if I have all the 5's sorted out at the beginning of the table (descendent sort). If I have the W column in crescent sort, with all the 1's first, then it does not copy any line to Sheet2. It gives the message All data copied.", but then no line is copied on the Sheet2.

Why is that? Can you help me with this matter?

`While Len(Range("A" & CStr(LSearchRow)).Value) > 0` Your loop is testing for something that can end before it reaches bottom of column? — findwindow, Dec 09 '15 at 16:37
findwindow, do you think that the line you state in your comment may be the source of the problem? I tested the version suggested by David Zemens, but his version also does not work when I have the W column sorted in crescent mode. Could you suggest a different approach to this macro? — Marcos Pereira, Dec 10 '15 at 16:29
Try using a for loop that will iterate through the entire column? — findwindow, Dec 10 '15 at 16:35

score 0 · Answer 1 · answered Dec 09 '15 at 16:44

This slightly modified version seems to work for me, even if data is not sorted. You may have a problem with range qualfication, or if you are running it while Sheet2 is active it may not copy anything as expected, etc.

Sub procurarnegociacion()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim source As Worksheet
Dim dest As Worksheet

On Error GoTo Err_Execute

Set source = Worksheets("Sheet1")   'Modify as needed
Set dest = Worksheets("Sheet2")     'Modify as needed

'Start search in row 5
LSearchRow = 5

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

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

        'If value in column W = 1, copy entire row to Sheet2
        If .Range("W" & CStr(LSearchRow)).Value = "5" Then

            'Select row in Sheet1 to copy
            .Rows(LSearchRow).EntireRow.Copy _
                 Destination:=dest.Rows(LCopyToRow)

            'Move counter to next row
            LCopyToRow = LCopyToRow + 1
        End If

        LSearchRow = LSearchRow + 1
    Wend
    'Position on cell A3
    Application.CutCopyMode = False
    .Range("A3").Select
End With

MsgBox "All data copied."

Exit Sub

Err_Execute:
MsgBox "Error ocurred."

End Sub

Excel VBA macros does not find matches

1 Answers1