Drop down boxes in VBA

[Haden]

Hi

I have two different drop down boxes on two different sheets. I want to make
a macro that will automatically change the second drop down box when someone
manually updates the first drop down box. I am currently using the code below
but it only works for the first person ("Adrienne Zwarts"). Can anyone help
me?

Sub Adviser()

Dim Adviser(2) As String
Dim i As Integer
Dim var

Adviser(0) = "Adrienne Zwarts"
Adviser(1) = "Andrew Cuttriss"
Adviser(2) = "Anna Tapp"

Sheets("Cover Letter").Select
If Range("D22") = Adviser(i) Then
Sheets("Nov09").Select
Selection.AutoFilter Field:=2, Criteria1:=Adviser(i)


End If

End Sub

P.S. I'm very new to this

 

[JLGWhiz]

Modify this:

If Range("D22") = Adviser(i) Then
Sheets("Nov09").Select
Selection.AutoFilter Field:=2, Criteria1:=Adviser(i)

End If

To this:

For i = 0 To 2
If Range("D22") = Adviser(i) Then
Sheets("Nov09").Select
Selection.AutoFilter Field:=2, Criteria1:=Adviser(i)
End If
Next

But it will still only match one of the three names. Why don't you explain
what you are trying to do and maybe someone can give you a solution that
will use all three names.