VBA Help with IF statement

[sweetness34]

Hi i need help with simple coding. I cant seem to come up with the right code for the program to understand.
here is my code

    If  ***       Then
 
Cells.Find(What:="Non-Conveyor Piping", SearchDirection:=xlNext, LookAt:=xlWhole).Select
Cells.Find(What:="Non-Conveyor Piping", After:=Range("A1"), SearchDirection:=xlPrevious).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert shift:=xlUp
Else

what i want to happen is search the spreadsheet for "Non-Conveyor Piping" and if it exists then execute the code. if it does not, then simply ignore the code and procede.

Thanks

 

[Joe4]

No im trying to find that hour value(in column H) and then copy it and paste it in another tab.

So what does the final result (this other tab) look like when it is completed?
It is really just a summary of your orignal sheet, or are things being pasted in certain cells on this "other" tab because there is alreadu information on this other tab that this data needs to be combined with?

 

[sweetness34]

yes it is being combined with other information.

 

[sweetness34]

Also can you tell me why I'm getting a runtime error?
Below is my code. It looks long but its really the same thing 3 times in a row just different search critiera. The red text is what gives me the error. I'm not sure why this happens but I can tell you that in my spreadsheet only "Pipe Supports" are present(for the test that gave me the error). So it searches for Conveyor Piping and does not find it so it should skip(which it does) to Non-Conveyor Piping then search for Non-Conveyor Piping. Now since it is not in my data it should get an error and skip to the next section of code, but apparently this doesnt work. It seems to work if 2 of the 3(Conveyor/Non-Conveyor/Pipe Supports) are present in the spreadsheet but if only 1 is present(2 not present) then it has an issue. I dont understand why this would be. ANY suggestions would be helpful.

    'Insert 1 row
        On Error GoTo exit_sub
        'search for "Conveyor Piping"
            Worksheets("imported raw data").Select
            Cells.Find(What:="Conveyor Piping", After:=Range("A1"), SearchDirection:=xlPrevious).Select
        'insert row after "Conveyor Piping" if it exists
            ActiveCell.Offset(1, 0).Select
            ActiveCell.EntireRow.Insert shift:=xlUp
        'sum Conveyor Piping
            Cells.Find(What:="Conveyor Piping", SearchDirection:=xlNext, LookAt:=xlWhole).Select
            x = Selection.Offset(0, 4).Address
            Cells.Find(What:="Conveyor Piping", After:=Range("A1"), SearchDirection:=xlPrevious).Select
            y = Selection.Offset(0, 4).Address
            z = Selection.Offset(1, 6).Address
            ActiveWorkbook.Worksheets("imported raw data").Range(z).Select
            Selection.Value = Application.WorksheetFunction.Sum(Range(x, y))
        'copy Conveyor to next tab
            Worksheets("imported raw data").Select
            Cells.Find(What:="Conveyor Piping", After:=Range("A1"), SearchDirection:=xlPrevious).Select
            z2 = Selection.Offset(1, 6).Address
            Range(z2).Select
            Selection.Copy
            Worksheets("Report Summary Page").Select
            Range("B4").Select
            ActiveSheet.paste
            Application.CutCopyMode = False
            Application.ScreenUpdating = True
        On Error Resume Next
exit_sub:
    Err.Clear
 
        On Error GoTo exit_sub1
        'search for "Non-Conveyor Piping"
            Worksheets("imported raw data").Select
            [COLOR=red]Cells.Find(What:="Non-Conveyor Piping", After:=Range("A1"), SearchDirection:=xlPrevious).Select[/COLOR]
        'insert row after "Non-Conveyor Piping" if it exists
            ActiveCell.Offset(1, 0).Select
            ActiveCell.EntireRow.Insert shift:=xlUp
        'sum Non-Conveyor Piping
            Cells.Find(What:="Non-Conveyor Piping", SearchDirection:=xlNext, LookAt:=xlWhole).Select
            x = Selection.Offset(0, 4).Address
            Cells.Find(What:="Non-Conveyor Piping", After:=Range("A1"), SearchDirection:=xlPrevious).Select
            y = Selection.Offset(0, 4).Address
            z = Selection.Offset(1, 6).Address
            ActiveWorkbook.Worksheets("imported raw data").Range(z).Select
            Selection.Value = Application.WorksheetFunction.Sum(Range(x, y))
        'copy Non-Conveyor Piping to new tab
            Worksheets("imported raw data").Select
            Cells.Find(What:="Non-Conveyor Piping", After:=Range("A1"), SearchDirection:=xlPrevious).Select
            z3 = Selection.Offset(1, 6).Address
            Range(z3).Select
            Selection.Copy
            Worksheets("Report Summary Page").Select
            Range("B5").Select
            ActiveSheet.paste
            Application.CutCopyMode = False
            Application.ScreenUpdating = True
        On Error Resume Next
 
exit_sub1:
    Err.Clear
 
'sum Pipe Supports
        On Error GoTo exit_sub2
            Worksheets("imported raw data").Select
            Cells.Find(What:="Pipe Supports", SearchDirection:=xlNext, LookAt:=xlWhole).Select
            x = Selection.Offset(0, 4).Address
            Cells.Find(What:="Pipe Supports", After:=Range("A1"), SearchDirection:=xlPrevious).Select
            y = Selection.Offset(0, 4).Address
            z = Selection.Offset(1, 6).Address
            ActiveWorkbook.Worksheets("imported raw data").Range(z).Select
            Selection.Value = Application.WorksheetFunction.Sum(Range(x, y))
        'Copy Pipe Supports to new tab
            Worksheets("imported raw data").Select
            Cells.Find(What:="Pipe Supports", After:=Range("A1"), SearchDirection:=xlPrevious).Select
            z1 = Selection.Offset(1, 6).Address
            Range(z1).Select
            Selection.Copy
            Worksheets("Report Summary Page").Select
            Range("B6").Select
            ActiveSheet.paste
            Application.CutCopyMode = False
            Application.ScreenUpdating = True
        On Error Resume Next
exit_sub2:
    Err.Clear

 

[Joe4]

I'm buried in a few things at work here.
I hope to take a look at this in a bit more detail a little later on.

I need to understand a little bit better the connection between your two sheets and how you determine what information to copy over and where to put it.

 

[sweetness34]

The connection between sheet1("import raw data" in my code) and sheet2("Report Summary Page" in my code) is just copying the value in a cell on sheet 1 to sheet 2. The location of the cell that needs to be copied is ALWAYS 1 row below and 4 columns to the right. For example if my string "3D Model Prep" in cell D10 then the cell that needs to be copied is H11. The cell that is copied(H11) will be pasted next to 3D Model Prep(cell B12, sheet2).

I wish I knew how to attach screenshots since that might be easier.

 

[Joe4]

Are we able to remove unwanted data on our "raw data" page?
Here is my thought:

- Copy the values from columns C/D to the row with the number in column H.
- Delete all rows without values in column H.

This would clean-up your row data into a nice table where only records with values in column H are left (and columns C and D are populated).

Now, armed with this data table, you should be able to use a VLOOKUP function to look up the values you need from this table for sheet 2.

 

[sweetness34]

Unfortunately that data needs to stay. The value I'm copying is actually a sum that has been offset how I explained earlier. You mentioned the VLOOKUP function how exactly does that work and what are the capabilities with it

 

[Joe4]

How about inserting a temporary "working" sheet where we can copy over our raw data and make it into a nice table that we can use in a VLOOKUP?

If you are unfamiliar with VLOOKUP, take a look at Excel's built-in help files for details and examples.

 

[Joe4]

Here is what the summarized "working" table might look like after clean-up, making it much easier to work with.

C                    D          H
Conveyor Piping    Prep        30
Conveyor Piping    Rev         20
Non-Conveyor       Prep        15
...

 

[sweetness34]

Yea that might work ill go ahead and work on that. On the other hand do u know why that error shows up even tho it should skip?