macro result with function key DIFFERENT then macro result with cell activation

[littlepete]

hello all

for me this is a real mysterie :

i have a macro that partially functions perfectly: it uses autofilter to get all rows that contain " FAM..." in the column CC.
when i use the CONTROL-i function key it works perfect !
The macro looks like this :

Sub familiefilter() ' ---------------------------------------------------------------------------------- ctrl - i
Application.ScreenUpdating = False
Dim dezerij As Long
dezerij = ActiveCell.Row
Dim dezefam As String
dezefam = Left(Sheets("gegevens").Range("cc" & dezerij).Value, 6)
ActiveSheet.Range("A:A").AutoFilter Field:=81, Criteria1:= _
"=*" & Left(Sheets("gegevens").Range("cc" & dezerij).Value, 6) & "*", Operator:=xlAnd
Application.ScreenUpdating = True
tooneersterij
End Sub

but, i am adding macro functionality to my third row, and assigned this macro to cell D3, as you can see below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("F3")) Is Nothing Then
            Call filteruit
            Range("a5").Select
        End If
        If Not Intersect(Target, Range("E3")) Is Nothing Then
           Call volledigscherm
        End If
      If Not Intersect(Target, Range("D3")) Is Nothing Then
          Call familiefilter
      End If
    End If
    Application.OnKey "{F3}", "datumfilterx"
    Application.OnKey "{F5}", "datumfilter"
    Application.OnKey "{F8}", "allerijhoogtenaanpassen"
    Application.OnKey "{F10}", "standardview"
    Application.OnKey "{F11}", "volledigscherm2"
    Application.OnKey "{F12}", "box"
End Sub

Each time that i try the family filter, that looks at the six first letters of column CC (FAM + three letters) the result is always 81 ... !
and 81 is exactly the number of the 81thd column, being CC !!!
how can that be ?

thank you for breaking your brain on this !

 

[VincentNL99]

What do you consider the result in this one?

 

[littlepete]

What do you consider the result in this one?

hello

well: it's an address list, and in column CC i start with a six letter keyword : FAM + the last name of the family for ex. : my family is FAMDJG family dejaeger

the autofilter selects "start with" + the six letters in column CC of the active row, this way selecting all family members with the same keyword-family name...

when i used control-i all is perfect... when i click on cell D3 to activate the same macro, the end result is always no visible rows,
and when i check the autofilter, it says : starts with ... 81 !!! (and in the column CC allllll cells start with FAM...

so i don't understand where that 81 (apart from being column CC, the 81th column) comes from...

 

[MARK858]

Why are you using ActiveSheet.Range("A:A") rather than ActiveSheet.Range("CC:CC") or ActiveSheet.Range("A:CC") in the line below?

ActiveSheet.Range("A:A").AutoFilter Field:=81, Criteria1

Your Field should be within the Range i.e.

ActiveSheet.Range("A:CC").AutoFilter Field:=81, Criteria1

or

ActiveSheet.Range("CC:CC").AutoFilter Field:=1, Criteria1

 

[littlepete]

hello

sorry to disappoint you,
i have tried:

dezefam = Left(Sheets("gegevens").Range("cc" & dezerij).Value, 6)
ActiveSheet.Range("a:cc").AutoFilter Field:=81, Criteria1:="=" & dezefam & "*", Operator:=xlAnd

and i tried:

dezefam = Left(Sheets("gegevens").Range("cc" & dezerij).Value, 6)
ActiveSheet.Range("cc:cc").AutoFilter Field:=1, Criteria1:="=" & dezefam & "*", Operator:=xlAnd

i also tried replacing the part " left(sheets ..." in the filtercode (activesheet ...) with the variable dezefam ...
first option in the original message, second option in here and current...

it still keeps "selecting" " 81 " (!!!) as item to be filtered... nowhere i can see where that is chosen...

good luck !!!

 

[MARK858]

sorry to disappoint you,

I'm not disappointed, I just asked why you were doing it

 

[littlepete]

what i do not see is why the same macro gives me two different results each time !!!
with control-i it always gives the correct result.
with clicking on cell D3 it always gives me no rows and a filter request for "81" !!!

i speak chinese, but this is worse !!!

 

[littlepete]

I'm not disappointed, I just asked why you were doing it

HELLO

this is really spooky...

i added a help column cj, which is column 88. in that column i only put as try out a few (20 first rows) fam keywords...
result: control - i works perfect... clicking on cell D3 gives me ... 88 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

i'm giving up for tonight, it 11.30 pm here... i hope you find a resolution, maybe you can write the macro differently?

good luck ! good night, and thank you !

 

[littlepete]

HELLO

this is really spooky...

i added a help column cj, which is column 88. in that column i only put as try out a few (20 first rows) fam keywords...
result: control - i works perfect... clicking on cell D3 gives me ... 88 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

i'm giving up for tonight, it 11.30 pm here... i hope you find a resolution, maybe you can write the macro differently?

good luck ! good night, and thank you !

because noone is really helping, just reading about my problem i have stopped trying. until today i have tried. now i stop and i thank all of you who think you should have done this or that.