VB in Excel: IF [text] is in a cell THEN...

[mguy27]

I'm kinda new to this VB in Excel thing, so bear with me on this one.

We have a program here at our facility which monitors thousands of alarm points. Activity of these alarm points can be exported to an excell file. An entry would look something like this:

Col. A Col. B Col. C Col. D
3/21/08 12:20:07 MPT 081 Forced Door Alarm

We get about 9500 of this per day. What we would like to do is create a macro where it starts at cell D1 and goes down the list. When it gets to a cell which contains the words "Forced Door" or "violated door" or "Door held open" etc, it will copy that entire row and paste it off to the side. So after we search through all 9500 per day, we end up with a list to one side of all the alarms of a particular type. My hang up is having the macro search the text within a cell. I was looking at something along the lines of this (which I know is incorrect):

Range("D2").Select
If Text = "%Violated Door%" Then
ActiveCell.Offset(0, 0).Rows("1:1").Select
Selection.Copy
ActiveCell.Offset(7, 2).Range("A1").Select
ActiveSheet.Paste
Else
Range("A10").Select
End If

Any help sould be greatly appreciated. Thanks!

-Taylor

 

[SkipVought]

As I stated in the other forum, the simplest and quickest way would be to QUERY the data using a criteria like I posted.

Could be accomplished in about 5 minutes.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[mguy27]

I posted it here again at your suggestion, and I will try that Query out and see how it goes. Thanks!

-Taylor

 

[SkipVought]

I would recommend...

1. Headings for your data (one row in ROW 1)
2. do not mess with your source data. Query the data to get the rows the either meet your statede criteria, or conversly the rows that do not meet your statede criteria.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}