How to match data from different columns between rows in VBA

Question

This is my first time using VBA/macros So I have an excel file with order numbers as the rows, line numbers as the columns, and then location numbers as the values within the cell.

I'm wanting set a current row and then loop through the rest of the rows with data in them to compare the values in the cells of these rows to the values in the set row and then count the number of matches. After I count them I want to place that matchcount number into a new column and then sort the entire sheet by matchcount so that I can take the top 9 rows and then move those ten (including the set row) into a new sheet.

This is what I have so far and it's getting all kinds of errors. Syntax is a big problem. I've been trying to figure out VBA by googling and so syntax is getting lost in translation. I'm also not sure how to paste data from one sheet into a new sheet while looping so that the new data being pasted goes to the next free row rather than pasting over the data already pasted.

Option Explicit
Sub Workbook_Open()

 ' This routine is called when the workbook is opened.
  With Worksheets("Sheet1")
    Dim ordercount As Integer
    ordercount = ActiveWorkbook.Worksheets("Sheet1").Range("A2", Worksheets("Sheet1").Range("A2").End(xlDown)).Rows.Count
    'create matchcount column
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Match Count"
    Columns("G:G").Select
    'create batchsheet
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("Sheet4").Select
    Sheets("Sheet4").Name = "batches"
    Sheets("Sheet1").Select
    Range("A1:G1").Select 'copying the headers into new sheet
    Selection.Copy
    Sheets("batches").Select
    Range("A1").Select
    ActiveSheet.Paste
End With
End Sub

Sub match()
Do Until ordercount = 0
    Dim currentrow As Long
    currentrow = 2
    Dim matchrange As Range
    Set matchrange = Worksheets("sheet1").Columns("B3:F")
    Location = Range("B2:F").Cells.Value
    For Each Row In matchrange
        Dim matchcount As Integer
        matchcount = 0
        For Each cell In Row
            If currentrow.Location = Location And Location > 0 Then 'I don't want the 0's to count as matches
            matchcount = matchcount + 1
        Next
        Column("G").Row.cell.Value = matchcount
    Next
    Call sort
    Call Remove
Loop
End Sub

Sub sort()
Columns("G:G").Select
ActiveWorkbook.Worksheets("Sheet1").sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").sort.SortFields.Add Key:=Range("G1"), _
    SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").sort
    .SetRange Range("G3:G")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub

Sub Remove()
Range("A2:G11").Select
Selection.Cut
Sheets("Sheet2").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Selection.Delete Shift:=xlUp
End Sub

Any help/advice would be appreciated. A lot of this was done using the record macros

Would you please show us a sample data, and clarify the business logic? Thanks and regards, — Alexander Bell, Jun 01 '15 at 15:37
I'm not a ten reputation so I can't add in screenshots. The worksheet has different order numbers in column A & the rest of the columns (B-F) are labeled as "Line 1", "Line 2", and so on. The corresponding cells contain an integer number (6, 72, 9, 0, etc) that correlates to a location where that line of product is placed. Orders can have product from multiple lines, so within one order row, column B could have the number 5, column D could have the number 66, the rest could be zero. The idea is that I'm trying to find a way to batch 10 orders together based on similar locations. — Lauren Neely, Jun 01 '15 at 16:40
so the orders with the most matched locations will be batched together. — Lauren Neely, Jun 01 '15 at 16:40
and then once that batch is complete, I want to remove those orders from that sheet and copy them into another sheet so that I can repeat the process without the same order being batched twice. — Lauren Neely, Jun 01 '15 at 16:42

score 0 · Answer 1 · edited May 23 '17 at 11:43

There are a lot of places that can be improved with basic methods for avoiding .Select and .ActiveCell. There also seems to be several places where the worksheet is implied and these should be specific.

This is a chop-down of the first routine.

Sub Workbook_Open()
    Dim ordercount As Integer
    ' This routine is called when the workbook is opened.
    With Worksheets("Sheet1")
        ordercount = .Range("A2", .Range("A2").End(xlDown)).Rows.Count  'what does this do?
        'create matchcount column
        .Range("G1") = "Match Count"
        'create batchsheet
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = "batches"
        .Range("A1:G1").Copy Destination:=Sheets("batches").Range("A1")
    End With
End Sub

The ordercount doesn't appear to do anything here but is referred to in another routine. Perhaps that should be a public variable or (better) either passed into the second routine or defined within the second routine. Selecting all of column G didn't appear to do anything so I removed it entirely.

Note that when you are within a With...End With statement, everything prefixed with a period (aka . or full-stop) like .Range or .Cells has its parent assigned as that which is in the With statement.

See How to avoid using Select in Excel VBA macros for more on methods to get away from relying on select and activate to accomplish your goals.

thank you so much. That definitely helped. And I'm not sure why I put the ordercount in that part, but I moved it into the second subroutine. Thanks again! — Lauren Neely, Jun 01 '15 at 16:32

How to match data from different columns between rows in VBA

1 Answers1