Filter data - date range and customer

[JonasB]

27820

Hello :)


According to the example above it should show records starting with ( like% ) the letters "tip" in the the specified "date range".

Please tell me what's wrong in the code below:

=====

Option Explicit


Sub data_controle()

Dim lngStart As Long, lngEnd As Long

Dim Repres As String

lngStart = Range("b4").Value

lngEnd = Range("c4").Value

Range("c12:m2000").AutoFilter Field:=1, _
Criteria1:=">=" & lngStart, _
Operator:=xlAnd, _
Criteria2:="<=" & lngEnd

Range("C13", Range("C13").End(xlDown)).Sort Key1:=Range("C13"), Order1:=xlAscending, Header:=xlNo

ActiveSheet.Range("$C$12:$M$2109").AutoFilter Field:=1, Criteria1:=">=" & lngStart, Operator:=xlAnd, Criteria2:="<=" & lngEnd

' the line below is not working ... ? - Repres

' ActiveSheet.Range("d4").AutoFilter Field:=2, Criteria1:=Repres & "*"


End Sub

=====

Thank you :friends:

[mancubus]

welcome to the forum.

assign a value to repres variable.


repres = "tip"

and remove the filter before sort

[mancubus]

my take on this:



Sub vbax_68340()
Dim LastRow As Long

With ActiveSheet
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("A12:M" & LastRow).Sort Key1:=.Range("C13"), Order1:=xlAscending, Header:=xlYes
.Range("A12:M" & LastRow).AutoFilter Field:=3, Criteria1:=">=" & CLng(Range("B4").Value), Operator:=xlAnd, Criteria2:="<=" & CLng(Range("C4").Value)
.Range("A12:M" & LastRow).AutoFilter Field:=4, Criteria1:="=Tip*"
End With
End Sub

[JonasB]

Hello VBAX Guru

Thank you. Almost perfect

It searches online customers beginning with "Tip".

.Range("A12:M" & LastRow).AutoFilter Field:=4, Criteria1:="=Tip*"

===

I need to search any customer's name. So "d4" has to be adjusted :)

.Range("A12:M" & LastRow).AutoFilter Field:=4, Criteria1:=d4

Thank you

[JonasB]

I think I got it :)

.Range("A12:M" & LastRow).AutoFilter Field:=2, Criteria1:=Range("d4") & "*"

[mancubus]

:thumb