Using VBA Match and Index function

  • Imagine my surprise to discover that the code we have been discussing isn't in that file.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Oh - you changed the control it applies to! You also left out the line that actually does the work. Please check the code I posted as you missed out this line:


    Code
    1. GetPartInfoForRange area.Columns(5), area.Columns(2), ODict


    in the For each area loop.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • This "Set ODict = GetDictionary(matchRange, 1, 2)" seems to be the issue it seems to say nothing.

    Should it be filling in with the cell values from the parts List?

    I`ve added your code above but still no luck?

  • Try this - it works for me once I added some data that matched up to the part list:


    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Please post your updated workbook then. As I said, it worked for me.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • First, there's no data in the job sheet in that file. Second, why do you keep changing the code I give you?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • No, it should have stayed as I had it.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Sorry seems to have stopped working


    This line says Sub or Function not Defined?

    outputRange.Cells(counter) = GetPartInfo(DataSet, cell.Value)


    Sub GetPartInfoForRange(lookupRange As Range, outputRange As Range, DataSet As Object)

    Dim cell As Range

    Dim counter As Long

    Dim ODict As Object


    For Each cell In lookupRange.Cells

    counter = counter + 1

    outputRange.Cells(counter) = GetPartInfo(DataSet, cell.Value)

    Next cell

    End Sub

  • It would only say that if you removed the GetPartInfo function code.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why