Solved: vba autofilter

[alienscript]

Hi,

Using BeforeDoubleClick event, I am auto-filtering the data in Sheet2 with reference to the Part Numbers in Sheet1, by double-click any part number in Column 3 of Sheet1.

I filtered Column 1 Sheet2 with Criteria1 as "=*" & var & "*" or contains. But now I want to filter column 1 in Sheet2 with only (or begin with) the first 10 characters of the part number in Sheet1 . I use Criteria1:="=RTRIM(left(ActifPN,10)) but didn't work. How do I tweak the code?

I'm using XL 2003. Appreciate any help and thanks a bunch.



Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim ActifPN
If ActiveCell.Column = 3 Then
ActifPN = ActiveCell.Value
Sheets(2).Select
If ActiveSheet.AutoFilterMode = False Then
Selection.AutoFilter
End If
Selection.AutoFilter Field:=1, Criteria1:="=*" & ActifPN & "*", Operator:=xlAnd
End If
End Sub

[Aussiebear]

What did you Dim ActifPN as?

[shrivallabha]

The length is less than 10 characters in at least one case in the sample data so how are you planning to tackle these?

[alienscript]

I finally got it fixed and worked.



Dim ActifPN As String
.........
If Not IsNumeric(Right(ActifPN, 1)) Then
Selection.AutoFilter Field:=1, Criteria1:="=*" & Mid(ActifPN, 1, Len(ActifPN) - 1) & "*", Operator:=xlAnd
Else
Selection.AutoFilter Field:=1, Criteria1:="=*" & ActifPN & "*", Operator:=xlAnd
End If
.........