Multi Text Criteria

[walkerke2]

Hi All VBAX,

This is my first posting and hope you guy's can get me going with VBA.

I have just started to work with large meter reading reports from a contractor at work.

I can record macros to do the simple things and copy some criteria but I need some direction or a start on logic.
I have attached a sample sheet which is small.

My Project is to gather info for reporting on these. To start I have in column B Route there are hundreds of these and I need to detect each route and then gather the Skips on column H which has Text and reads.
In column J I need to gather the reader info and System Admin.
In Column N I have trouble code info to match the failed audit in column N
a fail audit 2 should match a comment in column Q

I want to learn from this guys so if someone starts me off I will test and work hard to thinking this out.

Kenny:banghead:

[Bob Phillips]

I am sure that this isn't what you really want, but you can tell us what it needs after seeing it in action.

It copies all rows where column H is SKPRCL



Sub CopyData()
Dim rng As Range
Dim sh As Worksheet
Dim target As Worksheet

Set sh = ActiveSheet
Set target = Worksheets.Add
With sh

Set rng = Range(.Range("H1"), .Range("H1").End(xlDown))
.Columns("H:H").AutoFilter Field:=1, Criteria1:="SKPRCL"
rng.EntireRow.Copy target.Range("A1")
.Columns("H:H").AutoFilter
End With
End Sub

[mdmackillop]

Hi Kenny,
Welcome to VBAX.
First thing about progamming is to be clear what you are trying to achieve. I've read your post, looked at your data, and I've no idea what you are after. A "desired result" sheet together with some comments of your spreadsheet might give us a clue.
Regards
MD

[walkerke2]

Hi XLD,

That works a treat with that criteria. I will try and make it so I can add in more criteria and test today.
Big thanks:thumb

[walkerke2]

Hi MD,
Thanks for the Welcome. Just finding my way so just let me know if doing anything wrong.
XLD provided some code to start me with which I will try to expand on.
I also have attached another sheet to show you what I would like to end up with as a result of this project.

[Bob Phillips]

Whay are you not using formulae in that sheet to do all of the calculations?

[walkerke2]

Hi XLD,

I was only thinking of doing the searching and sorting out the elavant criteria needed first. That was my logical way of looking at this. Once I had filtered all the relavant data I was going to add in the count's so I can populate the report. Remember there are hundreds of these to do so if you have any time saving sugestions I'm all eyes and ears.
Thanks again

[Bob Phillips]

I can't see the data as you can, but I am assuming that all of the data is in one workbook, the summary is in another? I would think think that a set of SUMPRODUCT formulae will do those counts for you.

[walkerke2]

Hi XLD,

Yes I think the the formulae will be used once I can get my hear round all the date flows. Not sure if you seen my first sheet which is only a sample.
I get three to four of these sent to me everyday. There are initualy a list of routes and I need to know how many of each audit fail and comment there has been in each book.
Firstly do I need a lookup list or refrence to these and also the same with the skip code. as per below.
NE51011NE53011NE57011NE56011SE55011SE51011SW51011SW56011SW57011SW59011SW530 11NE63011NE66011NE67011NE61011SE65011SE61011SE62011SW69011SW61011SW68011SW6 6011 NE51012NE53012NE57012NE56012SE51012SE55012SW51012SW56012

SKUNMRSKUNPRSKPRCLSKZCDSKCHMRSKDUPLSKUNRESKWROGSYSTEM ADMIN

[Bob Phillips]

Not sure what you are asking me, but I repeat what I said in thread #8.

It seems to me that you originally wanted a subset of data, and pesumably this was to determine the numbers to plug into this spreadsheet. I am saying that you don't need to do that first step, you get all the numbers by formulae linked into the other workebook. You could even have a macro that automaticallys sets up the formulae.

[walkerke2]

OK XLD,

I think I know what your saying but a samll example might just point me in the right direction.
Big thanks for you time.

[Bob Phillips]

OK, this is calculating the number of SKPRCL in another workbook (Book1, Sheet4 in my case, change to suit). SO in K8 we use

=SUMPRODUCT(--([Book1]Sheet4!$H$2:$H$200=I8))

and in L8 we use

=K8/SUM($K$6:$K$15)

formatting the latter as a percentage.

I use SUMPRODUCT not COUNTIF so that it works with a closed workbook.

[walkerke2]

Hi XLD,
I'm working on the other sheet and I think these formulea are for the stat.xls which your SUMPRODUCT will work a treat.
The code you provided earlier is more what I'm lookinf for to filter the route's.
I will try this later. NE1011 See my first workbook I uploaded 1500 of these with 26 30 routes inside each sheet.
Many thanks
Kenny

[walkerke2]

Hi All,
Is there anyway to loop through text criteria. I have made changes to some of the code XLD kindly provided.
Sub CopyData()
Dim rng As Range
Dim sh As Worksheet
Dim target As Worksheet

Set sh = ActiveSheet
Set target = Worksheets.Add
With sh

Set rng = Range(.Range("H1"), .Range("H1").End(xlDown))
.Columns("H:H").AutoFilter Field:=1, Criteria1:="SKPRCL","SKUNM"
rng.EntireRow.Copy target.Range("A1")
.Columns("H:H").AutoFilter
End With
End Sub

[Bob Phillips]

You have to do an Or and add a Criteria2.

[walkerke2]

XLD I tried to find the do or stuff but found this. any examples much appreciated

Set rng = Range(.Range("H1"), .Range("H1").End(xlDown))
.Columns("H:H").AutoFilter Field:=1, Criteria1:="=S*"

[walkerke2]

Ok not sure guys if I'm going in the right direction see code below. be nice to she a new sheet for each of the searched criteria.
Sub CopyDataH20()
Dim rng As Range
Dim sh As Worksheet
Dim target As Worksheet

Set sh = ActiveSheet
Set target = Worksheets.Add
With sh

Set rng = Range(.Range("H1"), .Range("H1").End(xlDown))
.Columns("H:H").AutoFilter Field:=1, Criteria1:="=S*"

rng.EntireRow.Copy target.Range("A1")
.Columns("H:H").AutoFilter
ActiveSheet.Name = "Skips"
Set rng = Range(.Range("Q1"), .Range("Q1").End(xlDown))
.Columns("Q:Q").AutoFilter Field:=1, Criteria1:="=D*"

rng.EntireRow.Copy target.Range("A1")
.Columns("Q:Q").AutoFilter
ActiveSheet.Name = "Demolished"
End With
End Sub

[Bob Phillips]

I meant



.Columns("H:H").AutoFilter Field:=1, Criteria1:="SKPRCL",, Operator:=xlOr, _
Criteria2:="SKUNM"

[walkerke2]

Thanks XLD I did see xlor and looked at the examples, but being a newbie!
I'll test this option and see how it works.

Also ActiveSheet rename option only makes a new sheet with demolished and not one with Skips, I gess it's by passing.
Will have another go.

Cheers