Excel gefiltert Datenüberprüfung-Liste basierend auf Zellinhalt

Ich habe derzeit eine Tabelle, die wie folgt aussieht:

  |   A   |     B     |
  +-------+-----------+
1 | State | City      |
  +=======+===========+
2 | NSW   | Goulburn  |
3 | NSW   | Sydney    |
4 | VIC   | Melbourne |
5 | VIC   | Horsham   |
6 | NSW   | Tamworth  |

Und dann habe ich noch eine Tabelle, die wie folgt aussieht:

  |   A   |     B     |      C     |
  +-------+-----------+------------+
1 | State | City      | Other data |
  +=======+===========+============+
2 |       |           |            |

In dieser zweiten Tabelle habe ich angewandte Daten-Validierung sowohl des Staates und der Stadt Spalte, verweisen die Daten aus der ersten Tabelle. Also ich habe eine drop-down Liste von allen Staaten und Städten.

Was ich im Stande sein will zu tun ist, wenn der Benutzer eingibt "New South Wales" in der Status-Spalte der Liste der Optionen in der Stadt-Spalte gefiltert werden, um nur die Städte in New South Wales

  • Verwenden Sie eine Worksheet_Change Ereignis dynamisch zurücksetzen der Validierung, basierend auf den eingegebenen Wert in Spalte A.
  • Keine Ahnung wie, dieses Skript zu schreiben?
  • Ja. Siehe unten...
InformationsquelleAutor McShaman | 2013-03-28
  1. 1

    Legen Sie diese in das Arbeitsblatt ein code-Modul.

    Ändern Sie die definition von shTable zu finden, um das Arbeitsblatt, auf dem Sie Ihre lookup-Tabelle liegt.

    Private Sub Worksheet_Change(ByVal Target As Range)
Dim myVal As String
Dim cityList As String
Dim table As Range
Dim cl As Range
Dim shTable As Worksheet: Set shTable = Sheets("Index") '<modify as needed'

If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

myVal = Target.Value
With shTable
    Set table = .Range("A2", .Range("A2").End(xlDown)) 'contains your city/state table'
End With
    For Each cl In table
    'Build a comma-separated list of matching cities in the state.'
        If cl.Value = myVal Then
            If cityList = vbNullString Then
                cityList = cl.Offset(0, 1)
            Else:
                If InStr(1, cityList, cl.Offset(0,1).Value, vbBinaryCompare) > 0 Then
                'avoid duplicates, but this is not a foolproof method.'
                'probably should rewrite using an array or scripting dictionary'
                'otherwise the possibility of partial match is a potential error.'
                    cityList = cityList & "," & cl.Offset(0, 1)
                End If
            End If

        End If
    Next

'Now, with the cell next to the changed cell, remove '
' any existing validation, then add new validation '
' based on the cityList we compiled above.
With Target.Offset(0, 1).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=cityList
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

End Sub
    • Ihr Genie David! Zwei Fragen, wäre es leicht filtern Duplikate hier? Auch ist es möglich, verwendet benannte Bereiche anstelle der statische Verweis? Wenn ja, was ist die syntax?
    • Zum Beispiel, wenn Ihre Tabelle hat einen benannten Bereich attached to it, die Sie tun könnten Set table = .Range("Named Range") (entsprechend aktualisieren). Ich werde aktualisieren, die Antwort um Duplikate zu vermeiden.
    • Vielen Dank für deine Hilfe, David. Ich hatte zu ändern, der größer ist als ein symbol ">", um ein ist-gleich-Zeichen"=", aber außer, dass es perfekt funktioniert. Ich denke mein Nächster Schritt wird sein heraus zu arbeiten, wie die Arbeit mit arrays in VBA. Aber dieses Stück code hat sich sehr hilfreich für das Verständnis einiger Kern-VBA-Konzepte.
    InformationsquelleAutor David Zemens
Schreibe einen Kommentar