I am really new to VBA and I got stuck on a filtering function that I cannot resolve looking online.
I basically built a login form which allows different users to see different data on a "Report sheet" once they log in. In the "Report sheet" I have two columns "P" and "Q" in which some names are reported.
Let's say:
P Q
User1 User2
Paul Bob
Martin Martin
Bob Bob
Tom Martin
Ralph Bob
I then used this code on VBA:
Private Sub Worksheet_Activate()
Range("Report!$F$6:$Y$11").CurrentRegion.AutoFilter Field:=12, Criteria1:=Range("G3").Value
End Sub
**G3 is the reference cell for the name*
Now, if I login with Bob, using that code, the result will be:
P Q
User1 User2
Paul Bob
Bob Bob
Ralph Bob
That is correct! However, if I try to login with Paul, no result is available because he's not on the Field:=12 (column "Q").
I would like to do something like this: IF Paul is not on column "Q", stop filtering that column and start filtering column "P".
I tried with:
Private Sub Worksheet_Activate()
If Range("Report!$F$6:$Y$11").CurrentRegion.AutoFilter(Field:=12, Criteria1:=Range("G3").Value) Then 'do nothing
Else
Range("Report!$F$6:$Y$11").CurrentRegion.AutoFilter Field:=11, Criteria1:=Range("G3").Value
End If
End Sub
but I don't really know what to state for positive results.
Thank you in advance to everyone that will help me on that.
Aucun commentaire:
Enregistrer un commentaire