VB in Excel

[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]

Hi,

This post would be better served in either forum68 or forum707

Check out faq68-5829

I would probalu opt to use a query to return the data from the import that I was interested in. Assuming that column D heading value is AlarmType...

...
Where AlarmType Like '%Forced Door%' 
   OR AlarmType Like '%violated door%' 
   OR AlarmType Like '%Door held open%'

Skip,

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

 

[ryandepriest]

You might want to try "case" see the code below.

Sub find_instence()

Range("D1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "myRange"
For i = 2 To Range("myRange").Rows.Count + 1

mycase = ActiveCell.Value
Select Case mycase
Case "Forced Door"

Case "violated door"

Case "Door held open"

Case "nothing"
GoTo Skip
End Select
ActiveCell.Offset(0, 0).Rows("1:1").Select
Selection.Copy
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, -1).Range("A1").Select
Skip:
ActiveCell.Offset(1, 0).Range("A1").Select
Next i

End Sub