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
    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

  • 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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!