Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Match in VBA (Excel 2000)

    Please see attached workbook. I am running down a list and grabbing any row where the value of a cell in the first row of figures is greater than 12. As it runs through the code, it sometimes repeats a person who may be in the database twice. I do not want to filter first, I want to look at the first cell in the row, judge whether it matches the number in the first cell of any rows already grabbed and dumped onto the other worksheet, and if it does match, leave it alone and move down to the next row.

    I have tried applicationworksheetfunction.match and know how to use match in Excel, but get error messages. Any assistance would be appreciated.
    Attached Files Attached Files
    Neil Eustice
    Woody Worshipper

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Using Match in VBA (Excel 2000)

    Hi Neil,
    Attached is a workbook with some code that I think will do what you want. I've only done quick testing so it may need a tweak here and there but should get you started.
    Hope that helps.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Match in VBA (Excel 2000)

    WOW! I don't pretend to understand that at first glance. I will need to realy study it to work it out. I thought I could just use the match function to add one or two lines to the code I had. Thanks for this - I will try and work it out.
    Neil Eustice
    Woody Worshipper

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Using Match in VBA (Excel 2000)

    Neil,
    You could have used MATCH - I just like playing with arrays! Couldn't honestly tell you which would be faster but I try to avoid Copy/Paste whenever possible.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Match in VBA (Excel 2000)

    I am not totally clear on what you want to do. The following is my best guess:

    <pre>Sub getrows()
    Dim lRow As Long 'to hold the row number
    Dim lCol As Long 'to hold the column number
    Dim lLastRow As Long 'to hold the last row number
    Dim lPasteRow As Long 'To hold next paste row
    Dim I As Long, J As Long
    Dim bDup As Boolean
    lLastRow = Worksheets("Sheet1").Range("A1").Offset(Worksheets ("Sheet1").UsedRange.Rows.Count, 0).End(xlUp).Row
    lPasteRow = 0
    For lRow = ActiveCell.Row - 1 To lLastRow - 1
    ' You could do the following if you really want to use that "END STOP"
    ' You will also need a Wend instead of a Next lRow at the end
    ' Do While Worksheets("Sheet1").Range("A1").Offset(lRow, 0) <> "END STOP"
    For lCol = 0 To 2
    If Worksheets("Sheet1").Range("C1").Offset(lRow, lCol) > 12 Then 'if the next cell is >12
    bDup = False
    For I = 0 To lPasteRow - 1
    For J = 0 To 4
    If Worksheets("Sheet1").Range("A1").Offset(lRow, J) <> _
    Worksheets("Sheet2").Range("A1").Offset(I, J) Then
    Exit For
    End If
    Next J
    If J = 5 Then
    bDup = True
    Exit For
    End If
    Next I
    If (Not bDup) Or (lPasteRow = 0) Then
    Worksheets("Sheet1").Range("A1").Offset(lRow, 0).EntireRow.Copy 'Copy the entire row
    Worksheets("Sheet2").Paste Destination:=Worksheets("Sheet2").Range("A1").Offs et(lPasteRow, 0)
    lPasteRow = lPasteRow + 1
    End If
    Exit For
    End If
    Next lCol
    Next lRow
    Application.CutCopyMode = False
    End Sub
    </pre>


    One other comment. You should put your code into a module, not in the module behind the worksheet. That module has some special properties that could cause you some problems. Those modules are meant for event routines.

    I have attached a workbook with the code in it.
    Attached Files Attached Files
    Legare Coleman

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Using Match in VBA (Excel 2000)

    Neil,
    Thinking about it, you might be better off using the autofilter method, since built-in Excel functions are quicker than VBA versions generally. I'm attaching a revised version and the code is below too (I've put some comments in this time!):
    <pre>Sub CopyFilterList()
    Dim rngCopy As Range, lngLastRow As Long
    Application.ScreenUpdating = False
    With Sheets("sheet1")
    .UsedRange
    lngLastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
    Set rngCopy = .Range("A1", .Cells(lngLastRow, "E"))
    'Need titles for filtering
    .Range("A1:B1") = Array("FValue", "Name")
    'criteria range for filtering
    .Range("G1:G2") = WorksheetFunction.Transpose(Array("FValue", ">12"))
    'Filter on criteria, unique records only, copy to new sheet
    With rngCopy
    .AdvancedFilter xlFilterCopy, .Range("G1:G2"), _
    Sheets("Sheet2").Range("A1"), True
    End With
    'Remove titles and criteria range
    .Range("A1:B1").Clear
    .Range("G1:G2").Clear
    End With
    'Remove titles from copy
    With Sheets("Sheet2")
    .Range("A1:B1").Clear
    End With
    Application.ScreenUpdating = True
    End Sub
    </pre>

    Hope that helps.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Watford, Hertfordshire, England
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Match in VBA (Excel 2000)

    Well it certainly was fast. Very good. Two things though.

    It copies Bob, who has a value of 12 although I can see your code mentioning >12?

    Also, just to muddy the water a bit, what if I wanted to go down each column (C, D and E) looking at the same criteria (figure must be greater than 12). If someones figure in column D was >12 but they had already been pulled out, (use the item in column A which might be a staff number and would be unique), then ignore them as that row would have been pulled already. If the item in Column D was >12 and the "staff number" had not been pulled out before, then add that row to the collection.

    Thanks for the great help. I have attached the spreadsheet with some changed figures to try and simulate the above.
    Attached Files Attached Files
    Neil Eustice
    Woody Worshipper

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •