Using VBA Match and Index function

  • Mod note: Cross-posted here: Add ranges to match and index functions | MrExcel Message Board


    Trying to use match & Index with specified ranges


    Match, Index & function connect or talk to each other?


    See code below

  • Thanks for replying

    What I need is drawing No.s of the Parts list sheet column F into the job card Master sheet column B.

    The column E on both sheets match then the Drawing No in column B in Job Card Master to fill with the correct Drawing number from Parts List sheet Column F.


    Trust that makes sense

  • You've already got code that does that, so why do you need a different version?

    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

  • The code is supposed to be triggered by you changing the value of a combobox. What was wrong with the version you said was working over at the other forum?

    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

  • There is no mention of any specific ranges in your code or your question, so perhaps you need to clarify...

    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

  • I'm not sure you understand what I mean by "clarify"... ;)

    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

  • Saying "it does require ranges" really doesn't make anything clearer. Your code already uses ranges. What ranges do you mean? How should they be used?

    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

  • The ranges are pages 1 to 5

    The ranges need the Drawing numbers filled in from the Parts List sheet but not the 4 rows in between the pages.

    These are the ranges not part of this code but shows what I mean




    Select Case cmb.Value


    Case ("Break Lines 1 Page Job Card")

    colorAbove ws.Range("A13:Q" & LastRow)


    Case ("Break Lines 2 Page Job Card")

    colorAbove ws.Range("A13:Q61")

    colorAbove ws.Range("A66:Q" & LastRow)


    Case ("Break Lines 3 Page Job Card")

    colorAbove ws.Range("A13:Q61")

    colorAbove ws.Range("A66:Q122")

    colorAbove ws.Range("A127:Q" & LastRow)


    Case ("Break Lines 4 Page Job Card")

    colorAbove ws.Range("A13:Q61")

    colorAbove ws.Range("A66:Q122")

    colorAbove ws.Range("A127:Q183")

    colorAbove ws.Range("A188:Q" & LastRow)


    Case ("Break Lines 5 Page Job Card")

    colorAbove ws.Range("A13:Q61")

    colorAbove ws.Range("A66:Q122")

    colorAbove ws.Range("A127:Q183")

    colorAbove ws.Range("A188:Q244")

    colorAbove ws.Range("A249:Q" & LastRow)

    End Select

  • Does this code always need to process all those ranges, or does it depend on the value of whatever control cmb is, like that example?

    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 something like this:


    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

  • You're on the right track but?

    This part of the code below, wsDest.Range("B" & i).Value = GetPartInfo(ODict, wsDest.Range("E" & i).Value) says "method range of object worksheet Failed?"


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

    Dim cell As Range

    Dim counter As Long

    Dim ODict As Object

    Dim wsDest As Worksheet

    Dim i As Integer


    Set wsDest = ThisWorkbook.Worksheets("Job Card Master")


    For Each cell In lookupRange.Cells

    counter = counter + 1

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

    Next cell


    wsDest.Range("B" & i).Value = GetPartInfo(ODict, wsDest.Range("E" & i).Value)


    End Sub

  • Sorry, that line should have been deleted.

    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