1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating a custom filter using a drop down menu using Index Function

Discussion in 'VBA Macros' started by VB_Noob, May 1, 2018.

  1. VB_Noob

    VB_Noob New Member

    Messages:
    23
    Hi All,

    I'm creating a custom filter where it lists the data based on the criteria selection in a pull down menu. The custom filter seems to be working when all the data is in the same sheet.

    But now I want to move the source data into sheet2 and the pull down menu and show filtered data on sheet1. I'm using the index function and it is now working. Please take a look at the IFERROR INDEX function below and let me know what is wrong?

    IFERROR(INDEX(Sheet2!C2:E21,C3,COLUMNS($C$3:C3)),"")

    I got this sample code from the internet and is there a better way to create a custom filter?
  2. Khalid NGO

    Khalid NGO Excel Ninja

    Messages:
    1,923
    Hi,

    Can you please post a sample file with some data and expected result?

    Regards,
  3. VB_Noob

    VB_Noob New Member

    Messages:
    23
    Hi All,

    Please find attached file. Basically I want to see the form at the top of the form to display the data record per my input of the filter on the top right. Once the form shows the data and it should allow me to update the record using the form. I don't want the simple table layout because it is not very easy to browse and view when there will be up to 100 data fields in the sheet. Is there a way to do this?

    Thanks

    Attached Files:

  4. Marc L

    Marc L Excel Ninja

    Messages:
    3,866
    Hi !

    Easier is to just add a filter on Patient ID column of source table !
    (no needs any form, no needs any update procedure, no needs …)

    Or directly use the Patient ID B3 cell to load data
    and think about an update button.
    Better is to separate form and data table to different worksheets …
  5. VB_Noob

    VB_Noob New Member

    Messages:
    23
    A filter on patient ID column of source table is not something I want. Because there will be up to 100 data fields in that table. And some of them are long text field while other data fields need to be formulated to spits out data based on input data of adjacent cells. I don't want to have a very long horizontal table.

    Also I want to minimized number of sheets within a workbook as there will be many more sheets to be created within a workbook.
  6. Marc L

    Marc L Excel Ninja

    Messages:
    3,866
    As a beginner starter (you must complete the code for some form cells)
    paste this code to the worksheet module :​
    Code (vb):
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$N$3" Then
                V = Application.Match(Target.Value, Range("A21", [A20].End(xlDown)), 0)
                Application.EnableEvents = False
            If IsNumeric(V) Then
                V = 20 + V
                [B3].Value = Target.Value
                [D3].Value = Cells(V, 2).Value
                [F3].Value = Cells(V, 3).Value
           
            Else
                If Target.Value > "" Then Beep
                [B3,D3,F3,H3,B5,B7,B9,B11].Value = ""
            End If
                Application.EnableEvents = True
        End If
    End Sub
    Do you like it ? So thanks to click on bottom right Like !
    VB_Noob and Thomas Kuriakose like this.
  7. VB_Noob

    VB_Noob New Member

    Messages:
    23
    Thanks for your code Marc!
    So you are making a loop with counter of 20? If I have more data fields, then I would need to increase that 20 to whatever the number of time of the loops?

    I still want to be able to update the date using the form. Is it possible?
  8. Marc L

    Marc L Excel Ninja

    Messages:
    3,866

    A loop ‼ Where ?! :eek: (Not the best idea to use any loop here …)
    Just using the MATCH Excel worksheet function.

    To update you just have to code the reverse of worksheet Change event
    via a specific button … So first achieve this event.
  9. VB_Noob

    VB_Noob New Member

    Messages:
    23
    Isn't the following code generates a loop for 20 times?
    If IsNumeric(V) Then
    V = 20 + V
    'Then assigning value from button table to the cells of top form?
    [B3].value - Cells (V, 2).Value

    So I won't be able to update the data using the form on the top of the sheet without a "Update" button, right?
  10. VB_Noob

    VB_Noob New Member

    Messages:
    23
    Sorry, forgot to attach my file.

    Attached Files:

  11. Marc L

    Marc L Excel Ninja

    Messages:
    3,866
    Nope, no loop ‼ (worse idea !)
    Just read about MATCH function in Excel inner help …
    Instead of this function you can use the Find method
    to see in VBA inner help.

    To update, you can watch out cell address in the same Change event
    so without any button …
  12. VB_Noob

    VB_Noob New Member

    Messages:
    23
    I get an error message stating "End If without block if". I see I have four if and four end if. What's wrong.

    Also the code is not working. The code supposed to check if cell "D3" of the top table changed its value. If so, then "patient name" field of the table at the button of the sheet will change to "D3" of the table at the top of the sheet. I've also attached file. Thanks

    Code (vb):
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$N$3" Then
                V = Application.Match(Target.Value, Range("A21", [A20].End(xlDown)), 0)
                Application.EnableEvents = False
            If IsNumeric(V) Then
                V = 20 + V
                [B3].Value = Target.Value
                [D3].Value = Cells(V, 2).Value
                [F3].Value = Cells(V, 3).Value
                [H3].Value = Cells(V, 4).Value
                [B5].Value = Cells(V, 5).Value
                [B7].Value = Cells(V, 6).Value
                [B9].Value = Cells(V, 7).Value
                [B11].Value = Cells(V, 8).Value
                Else
                If Not Intersect(Target, Range("D3")) Is Nothing Then
                    Cells(V, 2).Value = [D3].Value
                   
                   
               
             
            Else
                If Target.Value > "" Then Beep
                [B3,D3,F3,H3,B5,B7,B9,B11].Value = ""
                Application.EnableEvents = True
                End If
                End If
            End If
        End If
           
           
       
       
    End Sub

    Attached Files:

  13. Marc L

    Marc L Excel Ninja

    Messages:
    3,866
    Well, you did not understand any logic
    as the code still watches only N3 cell !
    Your error comes from If / End If block not respected …

    As you also had some difficulties with the easy MATCH function to catch
    the source ID row, the next demonstration uses the VBA Find method,
    maybe a bit more difficult but easy once you read its VBA inner help
    and there is nothing to mod (just use it like it is !) :​
    Code (vb):
    Private Sub Worksheet_Change(ByVal Target As Range)
      Const C = "B3,D3,F3,H3,B5,B7,B9,B11,N3"
        Dim V, Rg As Range
        V = Application.Match(Target.Address(False, False), Split(C, ","), 0)
        If IsError(V) Then Exit Sub
        Application.EnableEvents = False
    Select Case V
        Case 2 To 8
          If [B3].Value > "" Then Set Rg = Me.UsedRange.Columns(1).Find([B3].Value, , xlValues, xlWhole)
            If Rg Is Nothing Then
                Beep
                Range(C).Value = ""
            Else
                Rg(1, V).Value = Target.Value
                Set Rg = Nothing
            End If
        Case 9
          If Target.Value > "" Then Set Rg = Me.UsedRange.Columns(1).Find(Target.Value, , xlValues, xlWhole)
            If Rg Is Nothing Then
                If Target.Value > "" Then Beep
                Range(C).Value = ""
            Else
                [B3:B11].Value = Application.Transpose(Array(Rg, "", Rg(1, 5), "", Rg(1, 6), "", Rg(1, 7), "", Rg(1, 8)))
                 [D3:H3].Value = Array(Rg(1, 2), [E3], Rg(1, 3), [G3], Rg(1, 4))
                        Set Rg = Nothing
            End If
    End Select
        Application.EnableEvents = True
    End Sub
    You should click on bottom right Like !
    VB_Noob likes this.
  14. VB_Noob

    VB_Noob New Member

    Messages:
    23
    Thanks. I will need to do some reading on the functions.

    Is VBA inner help a section on Microsoft website?
  15. Marc L

    Marc L Excel Ninja

    Messages:
    3,866
    As Match & Transpose are Excel worksheet functions
    to see in the Excel help and not within the VBA one …

    « inner » means the help inside Excel VBA !
    And you can find the same help on MSDN website …
  16. VB_Noob

    VB_Noob New Member

    Messages:
    23
    I'm still trying to mod the first program that you provided to give me what I need. I don't want to jump into the more complicated Find method before I can understand the first program. The range("A21", [A20].End9xlDown)),0) basically tells the program where to look for the source data and match with the data of the target value? A21 is the column and A20 through end of the sheet as the row?
  17. Marc L

    Marc L Excel Ninja

    Messages:
    3,866

    As A21 is a cell …
    The [A20].End(xlDown) is when on worksheet you select cell A20
    then you hit the combo keys Ctrl and down arrow
    so operating this manually what is the new active cell ?
  18. VB_Noob

    VB_Noob New Member

    Messages:
    23
    The last cell which contains a value (text or numeric).
  19. VB_Noob

    VB_Noob New Member

    Messages:
    23
    I apologize if my questions sound stupid as I am trying to understand the codes and do not want to use it without understanding it. I have a few questions below. Please correct if I misunderstood the code..

    Application.EnableEvents = False - I guess it is a good habit to use this to make sure no codes get to executed by accident? Why is this statement appears right after the below code? Wouldn't it stop executing the code?

    V = Application.Match(Target.Value, Range("A21", [A21].End(xlDown)), 0)
    The code will take the value of target address (cell: N3) and search it in the records that are located from row A21 to the last record on the sheet. I noticed there is no mention of column location. The zero means the code doesn't specify the number of returned result? What if I put 1 or 2 instead of 0?

    If IsNumeric(V) Then
    V = 20 + V
    [B3].Value = Target.Value
    [D3].Value = Cells(V, 2).Value
    [F3].Value = Cells(V, 3).Value
    -
    Above code is to check whether the variable V contain any numeric value or text value (string)? If V contains a numeric value, then change the value of V to 20 and use it as the location of the row (A20). The code then copies the value of cell (Row 20 and column 2) and paste it to cell B3, and repeat the operation for the rest of rows and columns.

    Else If Target.Value > "" Then Beep
    [B3,D3,F3,H3,B5,B7,B9,B11,B12.B13.B14].Value = ""

    - Above code basically says if the target value is not empty, then listed cells value should assign to nothing?? I'm not understand this.....

    And I also noticed the first IF statement is written before application.enableevent=false. Therefore, an end if is not needed for that IF statement? There are only two "end if" while total three If statements in the program.
    Last edited: May 10, 2018
  20. Marc L

    Marc L Excel Ninja

    Messages:
    3,866
    Yes !


    Now it's about my initial code, not about your mod :

    The procedure as a worksheet event is called when a cell value changes,
    disabling events before changing any other cell is necessary to avoid
    another call of this same event, like a snake trying to bite its own end …

    Wrong ! Of course there is a column location : A21 …
    As it's the MATCH worksheet function so just read why 0 in Excel help.
    So you'll see why with any other value than 0 this function may fail …

    After reading MATCH Excel help you'll understand
    this function returns only a numeric value when the value matches
    and an error when the value is not found …
    Just train with a MATCH formula directly within a cell.
    And V is not change to 20 !
    Place a break point in code (via F9 key) and when the code stops
    you can progress in code codeline by codeline via F8 key
    and see the variable value in VBE Locals window.
    As V must finally contain the found ID row # …

    Wrong ! Just if Target is not empty so sounds a beep …

    Yes as it is very not necessary to do anything if the condition is false …
    The reason why my second demonstration is more efficient.
    There are 2 syntaxes of If statement as you can see in VBA help,
    a single codeline (without End If) and
    a block of codelines (If … Then as first codeline and End If as last) …
  21. VB_Noob

    VB_Noob New Member

    Messages:
    23
    For some reason the step into feature doesn't work for me. I inserted the break point in the code and tried click on "step into" under Debug or by pressing "F8" key. It gives me a beep sound.

Share This Page