Code Check

M

Miya

Well-known Member
Joined
Nov 29, 2008
Messages
662
Hi, what is wrong with this code i get application defined object errror


With Sheets("SecuritiesReport").Range("A5")
.AutoFilter Field:=4, Criteria1:="=DOMCORP", Operator:=xlOr, Criteria2:="=TEMP", _
Operator:=xlOr, Criteria3:="=UNDADMIN", Operator:=xlOr, Criteria4:="=RIGHTS", _
Operator:=xlOr, Criteria5:="=SUBMVMNT"
End With
 
Sort by date Sort by votes
hi...
im makin movie database in access 2007 ...... i have thre tables as follow:
MOVIE
.movie title
.movieid
.year
.cast name
.genre
.director name
DIRECTOR
.directorid
.director name
.biography
CAST
.castid
.castname
.biography
i write following query to join these three table....
SELECT MOVIES.MovieTitle, MOVIES.MovieID, MOVIES.Year, MOVIES.[Cast Name], MOVIES.Genre, MOVIES.[Director Name]
FROM MOVIES INNER JOIN (MOVIES INNER JOIN DIRECTOR ON MOVIES.[Director Name] = DIRECTOR.[Director Name]) CAST ON MOVIES.[Cast Name]= CAST.[Cast Name];
on executin it show"syntax error in FROM clause"


pls help me........... i have to cmple this project within 3 days........ so pls help me.....
 
Upvote 0

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Hi Miya,

You can use named range for the criteria. Below is an example:

Before state (use macro FilterReset):
Excel Workbook
ABCD
1CriteriaHeader4
2DOMCORP
3TEMP
4UNDADMIN
5RIGHTS
6
7
8
9Database
10Header1Header2Header3Header4
111--Something
122--DOMCORP
133--Something
144--TEMP
155--UNDADMIN
166--Something
177--RIGHTS
188--DOMCORP
199--TEMP
Sheet


After applying of macro AdvFilter:
Excel Workbook
ABCD
9Database
10Header1Header2Header3Header4
122--DOMCORP
144--TEMP
155--UNDADMIN
177--RIGHTS
188--DOMCORP
199--TEMP
Sheet


The code:
Rich (BB code): 

' Advanced filter macro
' Add to the sheet Form's Button1 or AutoShape and link it to this macro
Sub AdvFilter()
  FilterReset
  DynamicRange
  Range("$A$10:$D$1000").AdvancedFilter xlFilterInPlace, Range("DynamicRange")
End Sub

' Reset filter
' Add to the sheet Form's Button2 or AutoShape and link it to this macro
Sub FilterReset()
  With ActiveSheet
    If .FilterMode Then .ShowAllData
  End With
End Sub

' Set dynamic range for criteria (for testing only)
Sub DynamicRange()
  Names.Add "DynamicRange", "=OFFSET($D$1:$D$8,,,COUNTA($D$1:$D$8))"
End Sub

Regards,
 
Upvote 0
Vladmir, is it possible to have the Autofilter turned on whilst the data is filtered via vba because as soon as i turn the filter it reverts back to all data.

ZVI said:
Hi Miya,

You can use named range for the criteria. Below is an example:

Before state (use macro FilterReset):
Excel Workbook
ABCD
1CriteriaHeader4
2DOMCORP
3TEMP
4UNDADMIN
5RIGHTS
6
7
8
9Database
10Header1Header2Header3Header4
111--Something
122--DOMCORP
133--Something
144--TEMP
155--UNDADMIN
166--Something
177--RIGHTS
188--DOMCORP
199--TEMP
Sheet


After applying of macro AdvFilter:
Excel Workbook
ABCD
9Database
10Header1Header2Header3Header4
122--DOMCORP
144--TEMP
155--UNDADMIN
177--RIGHTS
188--DOMCORP
199--TEMP
Sheet


The code:
Rich (BB code): 
' Advanced filter macro
' Add to the sheet Form's Button1 or AutoShape and link it to this macro
Sub AdvFilter()
 FilterReset
 DynamicRange
 Range("$A$10:$D$1000").AdvancedFilter xlFilterInPlace, Range("DynamicRange")
End Sub
 
' Reset filter
' Add to the sheet Form's Button2 or AutoShape and link it to this macro
Sub FilterReset()
 With ActiveSheet
   If .FilterMode Then .ShowAllData
 End With
End Sub
 
' Set dynamic range for criteria (for testing only)
Sub DynamicRange()
 Names.Add "DynamicRange", "=OFFSET($D$1:$D$8,,,COUNTA($D$1:$D$8))"
End Sub

Regards,
Click to expand...
 
Upvote 0
Miya said:
Vladmir, is it possible to have the Autofilter turned on whilst the data is filtered via vba because as soon as i turn the filter it reverts back to all data.
Click to expand...
You can’t use Advanced Filter and Autofilter simultaneously. Each disables another one.
But it is possible via VBA to hide rows according to criteria without disabling of autofilter.
 
Upvote 0
ZVI said:
You can’t use Advanced Filter and Autofilter simultaneously. Each disables another one.
But it is possible via VBA to hide rows according to criteria without disabling of autofilter.
Click to expand...

So for my example how can we get VBA to hide rows which do not conatain the criteria.

Or is there another way where we can actually use the autofilter but have 5-6 criterias via vba? Using Excel 2002
 
Upvote 0
Miya said:
is there another way where we can actually use the autofilter but have 5-6 criterias via vba? Using Excel 2002
Click to expand...
VBA can do all you wanted :)
See how to use VBA filter without interaction with AutoFilter:
Rich (BB code): 

Option Explicit
Option Compare Text

' ZVI:2011-07-06 http://www.mrexcel.com/forum/showthread.php?t=561925
' Filter Rng-range by Criteria
' Arguments:
'   Rng       - filtered range without header
'   Criteria - (optional) filter criteria(s), range_without_header)/value/array_of_values
' Note:
'   Missing of Criteria means ShowAllData in Rng-range
' VBA calls:
'   MyFilter Range("D11:D1000"), Range("D2:D5")
'   MyFilter Range("D11:D1000"), Range("DynamicRange")  NOTE: Don't include Header to DynamicRange
'   MyFilter Range("D11:D1000"), "DOMCORP,TEMP,UNDADMIN,RIGHTS"
'   MyFilter Range("D11:D1000"), Array("DOMCORP","TEMP","UNDADMIN","RIGHTS")
'
 Sub MyFilter(Rng As Range, Optional Criteria)
  Dim a, b, i As Long, r As Long, s As String, Sh As Worksheet, v, x
  On Error GoTo exit_
  If IsMissing(Criteria) Then Rng.EntireRow.Hidden = False: Exit Sub
  Set Sh = Rng.Parent
  a = Intersect(Sh.UsedRange, Rng).Columns(1).Value
  b = Criteria
  If Not IsArray(b) Then b = Split(b, ",")
  r = Rng.Row
  Application.ScreenUpdating = False
  Rng.EntireRow.Hidden = True
  With Sh
    For i = 1 To UBound(a)
      v = a(i, 1)
      If VarType(v) = vbString Then v = Trim(v)
      For Each x In b
        If x = v Then
          s = s & r & ":" & r
          If Len(s) >= 240 Then
            .Range(s).EntireRow.Hidden = False
            s = ""
          Else
            s = s & ","
          End If
          Exit For
        End If
      Next
      r = r + 1
    Next
    If Len(s) > 1 Then .Range(Left(s, Len(s) - 1)).EntireRow.Hidden = False
  End With
exit_:
  Application.ScreenUpdating = True
  If Err Then MsgBox Err.Description
End Sub

Sub SetMyFilter1()
  MyFilter Range("D11:D1000"), Range("D2:D5")
End Sub

Sub SetMyFilter2()
  MyFilter Range("D11:D1000"), "DOMCORP,TEMP,UNDADMIN,RIGHTS"
End Sub

Sub SetMyFilter3()
  MyFilter Range("D11:D1000"), Array("DOMCORP", "TEMP", "UNDADMIN", "RIGHTS")
End Sub

' Show all data in range
Sub ResetMyFilter()
  MyFilter Range("D11:D1000")
End Sub
 
Upvote 0
The improved version:
Rich (BB code): 

Option Explicit
Option Compare Text

' ZVI:2011-07-06 http://www.mrexcel.com/forum/showthread.php?t=561925
' Filter Rng-range by Criteria
' Arguments:
'   Rng       - filtered range without header
'   Criteria - (optional) filter criteria(s), range_without_header)/value/array_of_values
' Note:
'   Missing of Criteria means ShowAllData in Rng-range
' VBA calls:
'   MyFilter Range("D11:D1000"), Range("D2:D5")
'   MyFilter Range("D11:D1000"), Range("DynamicRange")  NOTE: Don't include Header to DynamicRange
'   MyFilter Range("D11:D1000"), "DOMCORP,TEMP,UNDADMIN,RIGHTS"
'   MyFilter Range("D11:D1000"), Array("DOMCORP","TEMP","UNDADMIN","RIGHTS")
 Sub MyFilter(ByVal Rng As Range, Optional Criteria)
  Dim a, b, i As Long, r As Long, s As String, Sh As Worksheet, v, x
  On Error GoTo exit_
  Set Sh = Rng.Parent
  Set Rng = Intersect(Sh.UsedRange, Rng)
  If IsMissing(Criteria) Then Rng.EntireRow.Hidden = False: Exit Sub
  a = Rng.Columns(1).Value: If Not IsArray(a) Then a = Split(a, Chr(0))
  b = Criteria: If Not IsArray(b) Then b = Split(b, ",")
  r = Rng.Row
  Application.ScreenUpdating = False
  Rng.EntireRow.Hidden = True
  With Sh
    For i = 1 To UBound(a)
      v = a(i, 1)
      If VarType(v) = vbString Then v = Trim(v)
      For Each x In b
        If x = v Then
          s = s & r & ":" & r
          If Len(s) >= 240 Then
            .Range(s).EntireRow.Hidden = False
            s = ""
          Else
            s = s & ","
          End If
          Exit For
        End If
      Next
      r = r + 1
    Next
    If Len(s) > 1 Then .Range(Left(s, Len(s) - 1)).EntireRow.Hidden = False
  End With
exit_:
  Application.ScreenUpdating = True
  If Err Then MsgBox Err.Description
End Sub

Sub SetMyFilter1()
  MyFilter Range("D11:D1000"), Range("D2:D5")
End Sub

Sub SetMyFilter2()
  MyFilter Range("D11:D1000"), "DOMCORP,TEMP,UNDADMIN,RIGHTS"
End Sub

Sub SetMyFilter3()
  MyFilter Range("D11:D1000"), Array("DOMCORP", "TEMP", "UNDADMIN", "RIGHTS")
End Sub

Sub SetMyFilter4()
  MyFilter Range("D11:D1000"), "DOMCORP"
End Sub

' Show all data in range
Sub ResetMyFilter()
  MyFilter Range("D11:D1000")
End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Macro to move data based on multiple filters in one column, part 2
Replies
2
Views
625
akg742
A
N
  • Solved
VBA: Autofilter xlOr Operator >than 2 Criteria
Replies
9
Views
4K
Fluff
Fluff
J
  • Solved
Search/Filter a Spreadsheet by User Entered Value
Replies
3
Views
197
jbennett01
J
Q
  • Question Question
VBA fill right
Replies
2
Views
735
Peter_SSs
Peter_SSs
C
  • Question Question
VBA: Filter specific data from table then make formula and put result to summary workbook
Replies
1
Views
602
HighAndWilder
H

Forum statistics

Threads
1,224,521
Messages
6,179,291
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top