Index match using VBA

  • Hi


    I'm trying to figure out how to write code for the following:


    I have an Excel table, which contains "cases". It has roughly 25 fields, filled through a userform with the following code:



    Private Sub CmdEnterNewCase_Click() Dim ws As Worksheet Dim lo As ListObject Dim newrow As ListRow Set ws = Sheets("Sheet1") Set lo = ws.ListObjects("Tbl_Input") With lo .AutoFilter.ShowAllData End With Set newrow = lo.ListRows.Add(Position:=1) 'put a new role at the top of the table With newrow 'add the userform inputs to the new row - number = column number .Range(1) = TxtCaseName .Range(2) = Format(CDate(Me.TxtDate.Value), "DD-MMM-YYYY") 'CDate converts the text to a date .Range(3) = CmbType .Range(4) = CmbPrimaryLocation .Range(7) = TxtLocalLead .Range(8) = TxtHQLead .Range(9) = Format(CDate(Me.TxtDate.Value), "DD-MMM-YYYY") & " - " & TxtSummaryCase & Chr(10) & Chr(10) & Chr(13) _ & "==================================START OF CASE RECORDS==================================" _ & Chr(10) & Chr(10) & Chr(13) .Range(12) = "NEW CASE" .Range(13) = CmbPRIVATE5 .Range(14) = CmbPRIVATE6 .Range(15) = CmbPRIVATE7 .Range(19) = TxtPRIVATE8 .Range(22) = TxtOtherContacts .Range(25) = TxtPRIVATE9 .Range(26) = Format(TxtDate, "DD-MMM-YYYY") 'puts case creation date into latest file update field .Range(26).NumberFormat = "dd/mmm/yyyy" 'formats the date as dd-mmm-yyyy // removes timestamp .Range(27) = Now & " - " & "Case created" .Range(29) = CmbPRIVATE10 .Range(30) = CmbStatus .Range(34) = TxtOtherCaseID .Range(54) = TxtLink .Range.EntireRow.WrapText = False 'sets the whole row to no text wrapping .Range.EntireRow.Font.Bold = False 'turns off the bold text of new role (picked up from title row in table) Application.CutCopyMode = False 'change text in relevant field to hyperlinks - see sub below ToHyperlink End With 'unloads the form Unload Me 'Show messgae box that new case added successfully MsgBox "New case created", vbInformation EndRoutine: 'Optimize Code Application.ScreenUpdating = True Application.EnableEvents = True 'Clear The Clipboard Application.CutCopyMode = False 'Close the userform FrmNewCase.Hide End Sub


    -/ I also have a module that adds "updates" to the table by matching the case name and adding a new row, with a few of the fields above filled in from a separate userform, e.g. date and "txtsummarycase". this module adds "CASE UPDATE" to .Range(12), where the new case module adds "NEW CASE"
    -/ I would like to incorporate code that pulls data from the new case row into the update row - for instance, the location (.Range(4)) of the case, which won't change. So, the "case update" code needs to match the case name from the NEW CASE row, and pull data from that row
    -/ I would normally do this with an Index/Match formula...


    Any help very welcome


    Phillipus

  • Hi,


    Indeed, you can use the Index/Match combination within a macro ...


    Why don't you attach your file ... for a tailor-made proposal ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks - I'm afraid the file has confidential info in it and it's hard to sanitise...


    I tried something like .Range(4)=appplication.worksheetfunctions(INDEX....) - but I couldn't get it to work... Is that the right ay to think about it?


    Thanks again

  • Yes ... that is the right way ...


    Code
    Application.WorksheetFunction.Index()
    and Application.Match()


    Hope this will help


    P.S. Watch out WorksheetFunction without s ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi this is the code i tried



    I get "1004: Unable to get the Match property of the worksheet function class"


    Also need to figure out how to make sure it pulls from the right row (with "NEW CASE" in .range(12))...

  • Code
    .Range(4) = Application.WorksheetFunction.Index(lo[COLOR=#FF0000][B].DataBodyRange[/B][/COLOR], Application.WorksheetFunction.Match(CmbSelectEntity, lo.ListColumns(1), 0), 4)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Can you test separately the Match section :


    Code
    Application.WorksheetFunction.Match(CmbSelectEntity, lo.ListColumns(1), 0)


    to find out if you are getting the right value ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • May I ask you :


    Are you the author of this macro ...?


    To drill down to what is generating the error ...


    Can you test CmbSelectEntity ?


    Is it a Value or a Text ?


    Are you sure this item is located in the First Column of your ListObject ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Sorry ... cannot help you any further ...:sniff:


    If the Text you are talking about ... is indeed located in the right Column ...


    There is absolutely no reason for the Match function not to return the Row Number ....

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • i should mention, the vales in the first column are not unique - there are multiple rows where cmbselectentity is "Case 1" (for example)... hence i need to also refer to range(12) where i note if it's a "NEW CASE" or "CASE UPDATE" - newrow needs to pull data from the row with "NEW CASE" in it...

  • i should mention, the vales in the first column are not unique - there are multiple rows where cmbselectentity is "Case 1" (for example)... hence i need to also refer to range(12) where i note if it's a "NEW CASE" or "CASE UPDATE" - newrow needs to pull data from the row with "NEW CASE" in it...


    This is adding a difficulty ...


    BUT ... it has nothing to do with the fact Match does NOT return the row number of the FIRST instance found ... :stare:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

Participate now!

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