VBA Help with IF statement

S

sweetness34

Board Regular
Joined
Jun 23, 2011
Messages
70
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
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
 
Sort by date Sort by votes
No im trying to find that hour value(in column H) and then copy it and paste it in another tab.
Click to expand...
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?
 
Upvote 0

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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.
Code: 
    '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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Here is what the summarized "working" table might look like after clean-up, making it much easier to work with.
Code: 
C                    D          H
Conveyor Piping    Prep        30
Conveyor Piping    Rev         20
Non-Conveyor       Prep        15
...
 
Upvote 0
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Procedure fails on some sheets only
Replies
2
Views
60
AlexanderBB
A
R
  • Question Question
cells.find error handling
2
Replies
13
Views
588
jolivanes
jolivanes
T
  • Question Question
Apply macro to all work sheets
Replies
3
Views
408
MARK858
MARK858
F
  • Solved
Looping code sequence
Replies
5
Views
428
FrenchCelt
F
SlinkRN
  • Solved
Handling Multiple Errors within one loop
Replies
7
Views
283
SlinkRN
SlinkRN

Forum statistics

Threads
1,224,521
Messages
6,179,286
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top