#1
|
|||
|
|||
How to Insert more criteria in Macro
I have the following macro below and I want to add more criteria into it. The criteria is:
For fieldName (Activity) in the Sheet, whenever it sees 4 under Activity field should change number to Book, 5 to Elephant, 6 to Hunter and so on. This way, am able to Filter and change Activity name all together when I run the code. All help appreciated. Thanks much! Sub Prices() 'Prices Macro ' Selection.AutoFilter ActiveSheet.listObjects("MyDatabse"). Range._ Autofilter Field:=4, Criteria1:="=yes", Operator:=xlOr, Criteria2:="=" ActiveSheet.ListObjects("MyDatabase").Range._ AutoFilter:=6, Criteria1:="=Complete", Operator:=xlOr,Criteria2_ :="=" Cells.Select Selection.Copy Sheets("Result").Select Range("A1").Select ActiveSheet.Paste End sub |
#2
|
||||
|
||||
I'll probably need to ask more questions, shilabrow, but let's start here: This code appears to have at least two syntax errors in it:
Code:
Sub Prices() Selection.AutoFilter ' | ' Invalid space here --------------- v ActiveSheet.listObjects("MyDatabse"). Range.Autofilter Field:=4, Criteria1:="=yes", Operator:=xlOr, Criteria2:="=" ' | ' Missing argument name ("Field", I suppose) here --- v ActiveSheet.ListObjects("MyDatabase").Range.AutoFilter:=6, Criteria1:="=Complete", Operator:=xlOr,Criteria2:="=" Cells.Select Selection.Copy Sheets("Result").Select Range("A1").Select ActiveSheet.Paste End sub Now, the real problem is that you're using features of which I'm ignorant; I don't know what ListObjects is and I almost never use Autofilter even manually. But before I ask more about those, can you show the real program, just to eliminate confusion (mine)? Better yet, can you just post the workbook? |
#3
|
|||
|
|||
Thanks BobBridges for your input. I have below the following code, you were right, I typed the code in hope the below helps because Its copied in - your help appreciated. The Listobjects is referencing database query, its pulling data from database. I want to add more criteria to it, I have a field named Activity in the table pulled from the database which has nos in them as 4, 5, 6 but I want them changed that is why I want to change those no to Book, Elephant, Hunter:
The criteria is: For fieldName (Activity) in the Table, whenever it sees 4 under Activity field should change to Book, 5 to Elephant, 6 to Hunter. Sub Trials() ' ' Trials Macro ' ' Selection.AutoFilter ActiveSheet.ListObjects("Table_Query_from_MS_Acces s_Database").Range. _ AutoFilter Field:=4, Criteria1:="Yes" ActiveSheet.ListObjects("Table_Query_from_MS_Acces s_Database").Range. _ AutoFilter Field:=6, Criteria1:="Complete" Cells.Select Range("Table_Query_from_MS_Access_Database[[#Headers],[First Name]]").Activate Selection.Copy Sheets("Result").Select Range("A1").Select ActiveSheet.Paste Range("A1").Select End Sub Thanks Much. |
#5
|
|||
|
|||
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Macro to insert Text | Morte | Excel Programming | 1 | 03-04-2014 04:33 PM |
MACRO - Insert row based on Form Field Criteria | Elan05 | Word VBA | 5 | 04-16-2013 06:39 AM |
Error on macro to insert endnotes on Mac | mbk | Word VBA | 11 | 12-20-2012 01:03 AM |
Macro to insert new page... | samanthaj | Word | 17 | 01-31-2012 01:53 PM |
Macro for Picture Insert | rfhall50 | Word VBA | 2 | 10-25-2010 12:41 PM |