none
Excel userform Index and match RRS feed

  • Question

  • Question, on index and match. Is there a way to convert this into VBA, so I can run it from the form and not from the worksheet?

    =IFERROR(INDEX(AlrmNo!$M$6:$M$1372, SMALL(IF(COUNTIF($D$22,AlrmNo!$A$6:$A$1372)*COUNTIF($D$18, AlrmNo!$K$6:$K$1372), ROW(AlrmNo!$M$6:$M$1372)-MIN(ROW(AlrmNo!$M$6:$M$1372))+1), ROW(A1)), COLUMN(A1)),"")

    thankyou


    heads up

    Wednesday, May 21, 2014 2:33 PM

All replies

  • Can you give a sample data of what you have data and what you want? It would be so much easier to deal with data than to decipher the formula you have.

    Happy to help ! When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answered

    Wednesday, May 21, 2014 2:35 PM
  • Sorry, so many code wizards here....

    So column A has a location code that I lookup based on a dropdown box result (from a xlsheetveryhidden). Then I have a date in a textbox Using this criteria, I want to look in a list xlsheetveryhidden, and determine the service tag numbers availible for the location and the dates associated with each one and populate a combobox or maybe a two column listbox with the results. In a perfect world, I would love this file to reside in the same directory because it could change. I'm just not sure I have the skills for that just yet...baby steps first. thank you

    Location      | Datecode   |asset number|servicetag|blah   |blah

    Wyoming32 | 3/24/2014 | 65432           | 77623     | west | NA

    Wyoming32 | 4/16/2014 | 85421           | 32653     | west | NA

    Wyoming32 | 6/29/2014 | 87901           | 6489221 | west | NA

    Colorado22  | 9/21/2014 | 65423           | 39023     | west | NA

    Colorado22  | 9/21/2014 | 13297           | 6783964 | west | NA


    heads up

    Wednesday, May 21, 2014 2:55 PM
  • Why don't insert location and datecode into arrays, and then search other values based on them?
    Thursday, May 22, 2014 8:46 AM
  • examples please?

    heads up

    Thursday, May 22, 2014 12:16 PM
  • Ok so maybe add control like OK button to the form, when you click on the OK the location and date will be read from the form, and then based on those two you can find other data in the very hidden sheet.

    Try with following:

    Private Sub cmdOK_Click()
       
    Dim location As String
    Dim dt As Date
        
        dt = Me.datee.Value 'textbox
        location = Me.loc.Value 'combobox
        
        for i = 1 to lastrow  'loop in the hidden sheet

          if cells(i,1).Value = dt AND cells(i,2) = location then

          'Here you are getting other data from cells(i,3), cells(i,4) etc.....

          exit for

          end if

        next i

       
    End Sub



    • Edited by T Krol Thursday, May 22, 2014 3:12 PM
    Thursday, May 22, 2014 3:10 PM