• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA code for Database filtering : troubleshooting

DAGUET

DAGUET

Member
Dear VBA Matter Experts
I currently work on a database from which I need to export and filter some data and bring it to another worksheet.
I filter the data from a date (date being a date and not a string) which is located in K5 of my worksheet where I intend to bring the data. This date is named 'mois'.
When executing the macro he tells me the usual

'Application-defined or object-defined error'


and upon debugging it highlights this part of the code in yellow:

ActiveSheet.Range("$A$8:$AC$104").AutoFilter Field:=28, Operator:= _
xlFilterValues, Criteria:=Array(1, "mois")

Maybe I misspecified my variable but I cant really tell
Any help would be useful
Many thanks


My subroutine below
-----------------------------------------------------------------------------
Code: 
Sub communication()
'
' communication Macro
'
Application.ScreenUpdating = False
'
Dim mois As Long
mois = ActiveSheet.Range("k5").Value
    Sheets("diplomation").Select
    ActiveSheet.Range("$A$8:$AC$104").AutoFilter Field:=28, Operator:= _
        xlFilterValues, Criteria:=Array(1, "mois")
    ActiveSheet.Range("$A$8:$AC$104").AutoFilter Field:=24, Criteria1:="="
    Range("B8:H150,M8:M150").Select
    Range("M8").Activate
    Range("B8:H150,M8:M150,AB8:AB150").Select
    Range("AB8").Activate
    Selection.Copy
    Sheets("network").Select
    Range("A8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("diplomation").Select
    Range("A8").Select
    Selection.AutoFilter
    Selection.AutoFilter
    Sheets("Network").Select
    Range("A1").Select
End Sub
Use << Code -tags >> please
 
DAGUET
Could You try to swap those 'Operator' <=> 'Criteria'?
from
Code: 
Operator:= xlFilterValues, Criteria:=Array(1, "mois")
to ... as below
Code: 
Criteria:=Array(1, "mois"), Operator:= xlFilterValues
 
Dear vletm

do you mean this way?

Dim mois AsLong
mois = ActiveSheet.Range("k5").Value
Sheets("diplomation").Select
ActiveSheet.Range("$A$8:$AC$104").AutoFilter Field:=28, Criteria:=Array(1, "mois"), Operator:= xlFilterValues
 
DAGUET
Yes!
1) That was Protected sheet ... and there were not allow to do Filter
2) This won't work neither!
Code: 
Dim mois As Long
mois = ActiveSheet.Range("k5").Value
later Your mois is String
Code: 
Array(1, "mois")
 
DAGUET
Why confused?
This version works better ...
I modified as less as possible.
There could make more modifications ...
 

Attachments

  • Maquette-2.xlsm
    431.9 KB · Views: 4
vletm said:
DAGUET
Why confused?
This version works better ...
I modified as less as possible.
There could make more modifications ...
Click to expand...
Apologies
that was not what I meant when I said 'confused'. I had difficulty to see which changes you made that is why.
Now with the file I saw it and it sounds to work.
Thanks again for your efforts and help*
much appreciated

best
 
You must log in or register to reply here.
Back
Top