INDEX/Match help (VBA eventually)

That's me also! Posted it in the other place to get help with excel in general. I then posted it here too to get help with the VBA part of it.

So here is the question simplified,

I need to grab all the WO (in col. A) that have an auction number of 39 (in col. C) and place the values in an array. Just a simple list of the WO that are going to be in auction 39.

hahah oops I didn't read the above post that cleared up all my questions

just taking a quick look at this, where is the input box or where would you like it to be for the user to input the sale# into to start and retrieve all the data? also is all this data supposed to be shown on the current data sheet?

I did this using an advanced filter but for some reason the site will not allow me to upload it so heres the code, you will have to change on work sheet "currentauction", swap columns G and H around

Option Explicit

Sub ButtonPress()
    
    Dim RowMatch As Variant      'Row number of the matched Auction Number
    Dim AuctionNum As Integer    'the auction number
    Dim AuctionCars As Integer    'number of cars in the inputed auction
    Dim AuctionSeller As String    'company selling the cars
    Dim SaleYear As String         'auction year pulled from manheim car database
    Dim IRange As Range
    Dim ORange As Range
    Dim Crange As Range
    Dim CarTimeArray() As String  '2-D array (WO, car completion time)
    Dim arrayRng As Range         'the range in which the CarTimeArray() is stored

    Dim i, j, k, FinalRow As Integer        'declaration variables for loops
    
    Dim ws1, ws2, ws3, ws4, ws5, ws6 As Worksheet
    Set ws1 = ThisWorkbook.Sheets("Daily Auction Sheet")
    Set ws2 = ThisWorkbook.Sheets("Database")
    Set ws3 = ThisWorkbook.Sheets("Data")
    Set ws4 = ThisWorkbook.Sheets("CurrentAuctionData")
    Set ws5 = ThisWorkbook.Sheets("Hidden")
    Set ws6 = ThisWorkbook.Sheets("Manheim Car Data")
    
    'prompts the user to enter the auction number then stores it in the variable AuctionNum,
    'the value is then placed in cell c6 of the workbook
    AuctionNum = Application.InputBox("Please enter the auction number", "Next Auction Data", Type:=1)

    '*******************
    If AuctionNum = 0 Then Exit Sub 'User Canceled
    '********************

    'Finds the AuctionNumber and stores it in a variable
    ws1.Range("C8").Value = AuctionNum
    
    'Match Auction Number and return the row number of 1st match
    RowMatch = Application.Match(AuctionNum, ws6.Range("C:C"), False)
    
    'places information on the current auction in the appropriate cells
    If Not IsError(RowMatch) Then   'Test if match was found
        SaleYear = ws6.Range("B" & RowMatch).Value 'Return Value from column B of the matched row
        ws1.Range("C9").Value = SaleYear

        '*** Taken out because different sellers per auction****
        'AuctionSeller = ws2.Range("D" & RowMatch).Value 'Return Value from column D of the matched row
        'ws1.Range("C10").Value = AuctionSeller

        AuctionCars = Application.CountIf(ws6.Range("C:C"), AuctionNum) 'Count rows that have the auction number
        ws1.Range("C11").Value = AuctionCars
        
         
  '*****ADVANCED FILTER******
FinalRow = ws6.Cells(Rows.Count, 1).End(xlUp).Row

'set criteria range
ws1.Cells(7, 3).Value = ws6.Cells(1, 3).Value
Set Crange = ws1.Range("c7:c8")

'add header and set output range
ws4.Range("b2:g2").Value = Array(ws6.Cells(1, 3), ws6.Cells(1, 7), ws6.Cells(1, 1), ws6.Cells(1, 5), ws6.Cells(1, 12), ws6.Cells(1, 4))
Set ORange = ws4.Range("B2:g2")

'Input range
Set IRange = ws6.Range("a1").Resize(FinalRow, 12)

'do the advanced filter
IRange.AdvancedFilter xlFilterCopy, Crange, ORange

'erase all background color
ws4.Range("a1").CurrentRegion.Interior.ColorIndex = xlNone
        
        
        
    Else
        MsgBox "No match found for " & AuctionNum, , "Auction Number Not Found"
        ws1.Range("C8:C11").ClearContents

    End If

    'sets up the current auction database
  '  For i = 3 To AuctionCars + 2
        '*********Need to clear contents before updating***

      '  ws4.Cells(i, "B").Value = AuctionNum
       ' ws4.Cells(i, "C").Value = SaleYear
        'ws4.Cells(i, "D").Value = Application.WorksheetFunction.Vlookup(ws4.Cells(i, "B").Value, ws6.Range("
        'ws4.Cells(i, 5).Value = ws2.Cells(i, 5).Value 'Car type
        'ws4.Cells(i, 6).Value = ws2.Cells(i, 6).Value 'Car ID
        'adds the eta for each type of car in column 7 of the CurrentAuctionDatabase
        'ws4.Cells(i, 7).Value = Application.WorksheetFunction.VLookup(ws4.Cells(i, 5).Value, ws3.Range("B3:C6"), 2, False)
   ' Next i


    'Sets up the Array CarTimeArray (A 2-D array with times of each car)
    '(Work order number, Car completion Times)
    ReDim CarTimeArray(1 To AuctionCars, 1 To 2)
    For i = 1 To AuctionCars
        CarTimeArray(i, 1) = ws4.Cells(i + 2, 6).Value
        CarTimeArray(i, 2) = ws4.Cells(i + 2, 7).Value
    Next i


    'pastes the array in the hidden tab (ws5) to test the values
    For i = 1 To AuctionCars
        ws5.Cells(i + 1, 2).Value = CarTimeArray(i, 1)
        ws5.Cells(i + 1, 3).Value = CarTimeArray(i, 2)
    Next i

    'Sorts the CarTimeArray in descending order (highest to lowest times)
    
   

    'sets up which lane the cars go into in a table
    'For j = 3 To AuctionCars + 2
     '   For k = 9 To 14
      '      ws4.Cells(j, 8).Value = Application.WorksheetFunction.Match(Application.WorksheetFunction.Min(ws4.Range("I2:N2")), ws4.Range("I2:N2"), 0)
            

       ' Next k
    'Next j
    

    
End Sub

Thank you both so much for the help. Sorry I'm late getting back to y'all as it was my birthday yesterday (and I'm in college haha). Running through everything now.

Matt

If any of our posts helped you please mark as solvef