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
    1. Application.WorksheetFunction.Index()
    2. 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:)

  • Could you ... at the very least ... post the macro which generates this error ...

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

  • At first glance ... think you should test


    lo.DataBodyRange


    Hope this will help

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

  • Code
    1. .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
    1. 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:)

  • hi - yes, i wrote the macro (adapted from code i found online)...


    cmbselectentity is text and gets placed in the first column of lo, with the macro (.range(1)) - inputted through the userform

  • 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:)

  • i tried:


    test = Application.Match(CmbSelectEntity, lo.ListColumns(1), 0)
    MsgBox test


    and got a type mismatch - hovering over cmbselectentity shows it's pulling the right value/text... does that help debug?