PDA

View Full Version : Search criteria, cut, and paste in new worksheet



JJBPSU
10-05-2010, 08:58 PM
I am new to VBA and stuck!!

I'm trying to write code to search for multiple values (total of 31 different values) from column "T" in "worksheet 5". Two example of multiple values include, "22400" and "22411".

I want to take each value found from search== cut the entire row for each value found and paste into new worksheet.

I'm stuck with writing code for more than 1 value.


:banghead:

Aussiebear
10-05-2010, 10:04 PM
What code do you have so far?

JJBPSU
10-05-2010, 10:09 PM
Hi there- here is what I have so far. This is my first macro project that isn't "basic".


Application.ScreenUpdating = False
'Worksheets("Raw Data").Activate
'FilterCriteriaBF = ActiveSheet.Range.Value2
'Range("FilterKey") = FilterCriteriaBF
'Clear Contents
'Sheets("Raw Data").Select
'Range("A1:AJ" & LastRow).Select
'Selection.ClearContents
'Range("A1").Activate
Sheets("Raw Data").Range("A1:AJ" & lastrow).ClearContents
'AutoFilter
Worksheets("Raw Data").Range("A1:AJ1" & lastrow).Formula = =OR("T1=22400,T1=22411,T1=22412" & _
,T1=22413,T1=22415,T1=22416)"
Worksheets("Raw Data").Range("A1:AJ" & lastrow).autofilter Field:=20, Criteria1:=True
'Copy
'Worksheets("Raw Data").Select
'Range("A1:AJ" & LastRow).Select
'Selection.Copy
'Worksheets("ExclFellowInternResidentSumYth").Select
'Range("A1").Select
'ActiveSheet.Paste
Worksheets("Raw Data").Range("A1:AJ" & lastrow).Copy Destination:=Worksheets("FilterData").Range("A1")
'AutoFilter Off
Worksheets("Raw Data").Range("A1:AJ" & lastrow).autofilter
'Sheets("ExclFellowInternResidentSumYth").Select
'Range("H6").Select
'Selection = 1
Sheets("ExclFellowInternResidentSumYth").Range("H6") = 1
Application.ScreenUpdating = True

Aussiebear
10-06-2010, 02:17 AM
Okay, just a couple of quick hints firstly

1. 8 lines out of 29 presented lines are only the actual working section of code. Makes it hard reading.

2. Be cautious when using macro recoded code.

Even though its commented out, things such as

Worksheets("Raw Data").Select
Range("A1:AJ") & LastRow).Select
Selection.Copy

Can be better written, such as


With Worksheets("Raw Data")
.Range("A:AJ") & LastRow.Copy

And while we are talking about commenting code out, a single apostrophe is all that is required. I amended your presented code to make it more readable.

This the actual working section of the code you have provided.

Application.ScreenUpdating = False
Sheets("Raw Data").Range("A1:AJ" & lastrow).ClearContents
Worksheets("Raw Data").Range("A1:AJ1" & lastrow).Formula = =Or("T1=22400,T1=22411,T1=22412" & _
,T1=22413,T1=22415,T1=22416)"
Worksheets("Raw Data").Range("A1:AJ" & lastrow).autofilter Field:=20, Criteria1:=True
Worksheets("Raw Data").Range("A1:AJ" & lastrow).Copy Destination:=Worksheets("FilterData").Range("A1")
Worksheets("Raw Data").Range("A1:AJ" & lastrow).autofilter
Sheets("ExclFellowInternResidentSumYth").Range("H6") = 1
Application.ScreenUpdating = True



When writing formulas in vba, the following won't work
Worksheets("Raw Data").Range("A1:AJ1" & lastrow).Formula = =Or("T1=22400,T1=22411,T1=22412" & _
,T1=22413,T1=22415,T1=22416)"

because you are missing the double quotation in front of the second equals sign. This may be just a typo but you need to check in your actual code.

Now we get down to the actual logic of the code.
You attempt to clear contents of the range A1to AJ1 & down to the last row, then replace it with an "or" formula. How does this work?