Help with a function to Find, insert and find next

[Boomstick]

Hi all,
Another newbie here, I have quite a conundrum that I have been working on and have not been able to figure out. I have a worksheet that will be used as a project checklist. This Checklist will have to be completed before projects can be approved. I need the user to select information about the project type from a drop down list in a worksheet, and depending on the information provided, I need excel to insert rows beneath that cell that was updated. The drop down list is in 10 cells in subsequent rows, and i need the program to run through the cells finding all the cells with the value "Pressure Vessel". I have created the following code:

Sub AutoOpen()
Worksheets("Sheet1").OnEntry = "CheckIt"
End Sub

Sub CheckIt()

Dim i As Integer
Dim rng As Range
Dim fnd As Range

i = 1
rng = ("B15:B100")

With Worksheets(Sheet8).Range(rng)
Set fnd = Selection.Find(What:=("Pressure Vessel"), LookIn:=x1Values, _
LookAt:=x1Part, SearchOrder:=x1ByRows, SearchDirection:=x1Next, MatchCase:=True, _
SearchFormat:=False)

If Not fnd Is Nothing Then
Rows("8:12").Select
Selection.Copy
Offset(Rows(fnd),1).Select
Selection.Insert Shift:=xlDown

Do
fnd.Value = "Pressure Vessel"
Set fnd = Cells.FindNext(fnd)
If Not fnd Is Nothing Then
Rows("8:12").Select
Selection.Copy
Offset(Rows(fnd),1).Select
Selection.Insert Shift:=xlDown
End If
Loop While Not fnd Is Nothing
End If
End With
End Sub

I have the cells that i would like to insert in rows 8:12. They are hidden so the user wont see them until they are inserted.

Like I said, I am a newbie. I have spent a couple days reading up on excel VBA, learning about the functions and errors, and trying to figure it all out, but I am at my wits end. Any help would be greatly appreciated!

Thank You

[Richard Buttrey]

Hi,

What's the overall task here?

Are you wanting a check list to be fully completed and on completion of all the checks you then want something to happen.
If so what exactly is that something? We just need to know exactly what you want to see, not how you think you should get there.

It would be useful if you could upload a workbook with a before and after situation. i.e. insert a second result sheet formatted and completed in the way it should look after your check list on the first sheet is complete, and explain why you show the results you do.

[ragulduy]

Maybe something like:

Please Login or Register  to view this content.

[Boomstick]

Richard,

Sorry I wasn't very clear, I am attaching a sample of what I would like the Sheet to look like. in the Worksheet 1 I have a sample of what the sheet will look like before information is selected. In Worksheet 2 I have an example of what i would like the worksheet to look like after the information has been selected from the drop down list. rows 1:17 will be hidden in the final draft. I'm not sure how relevant that is though. What I would Like is for the user to select equipment type from the drop down list from the rows that are in red. when the equipment type is selected, IE pressure vessel or Pressure Relief Valve, certain rows relevant to that equipment type are inserted below that cell.

I feel like I am asking a lot, for which i apologize, so any help is appreciated.
Sample.xlsx

[Richard Buttrey]

Hi,

Attached is a different approach.

Sheet 2 starts out with rows 24:33 hidden
There are a couple of helper cells C34 & C35 (they could be located anywhere) which are named "Check_Vessel" & "Check_valve" respectively. These count the number of instances of the text "Pressure Vessel" or "Pressure Relief valve" in occur in B34:B41

If either of the two helper cell contains a value >0 then the relevant rows on sheet 2 are unhidden