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]

We can make use of error handling to do this. If it does not find the phrase, it throws back an error message. We can use this to our advantage.

If it does not find the phrase (and hence we get an error), we can tell it to go to the end of the code (and in essence not process any code after the Find command). If it does find the phrase, it will continue to run all the code beneath it.

Here is what that code would look like. In my example, I return a simple message box saying "Found it!" to show it will run the code if it finds the phrase (but doesn't if it cannot find it).

Sub MySearch()
 
    On Error GoTo exit_sub
    Cells.Find(What:="Non-Conveyor Piping", After:=Range("A1"), SearchDirection:=xlPrevious).Select
    On Error GoTo 0
 
    MsgBox "Found it"
 
exit_sub:
    Err.Clear
 
End Sub

 

[sweetness34]

Thanks alot!!
It worked PERFECT after I added in the 10 other things I wanted it to do. It took me forever to get it all put together right though.
Thanks again!!

 

[sweetness34]

We can make use of error handling to do this. If it does not find the phrase, it throws back an error message. We can use this to our advantage.

If it does not find the phrase (and hence we get an error), we can tell it to go to the end of the code (and in essence not process any code after the Find command). If it does find the phrase, it will continue to run all the code beneath it.

Here is what that code would look like. In my example, I return a simple message box saying "Found it!" to show it will run the code if it finds the phrase (but doesn't if it cannot find it).

Sub MySearch()
 
    On Error GoTo exit_sub
    Cells.Find(What:="Non-Conveyor Piping", After:=Range("A1"), SearchDirection:=xlPrevious).Select
    On Error GoTo 0
 
    MsgBox "Found it"
 
exit_sub:
    Err.Clear
 
End Sub

what does the "On Error GoTo 0 mean?
also im trying to use this multiple times is there a way I can do that? i've tried using exit_sub , exit_sub1 , exit_sub2 ,etc.. but it doesnt seem to work 100%
basically I have a few sections of code that I want to run if certain critera exists if not and it gives me an error then skip that code.

 

[Joe4]

what does the "On Error GoTo 0 mean?

It simply means go back to the standard way it handles errors (with a pop-up error message).

If you have a lot of sections like this where you are searching, you may want to try something like:

On Error Resume Next

which I think should just ignore the error and go on to the next statement.

If that does not work for you, post your new code so we can see what you have.

 

[sweetness34]

That worked great thanks!!
but I've run into a problem unrelated to the previous issue so I'm hoping you can steer me in the right direction.

This is what my spreadsheet looks like

___A__|__B_|_______C_______|__D___|___E___| ____F___|___G___|___H
___________| Conveyor Piping | Prep |________|________|_______|____
___________| Conveyor Piping | Prep |________|________|_______|____
___________| Conveyor Piping | Prep |________|________|_______|____
___________|_______________|____|________|________|_______| 30
___________| Conveyor Piping | Rev |________|________|_______|____
___________| Conveyor Piping | Rev |________|________|_______|____
___________| Conveyor Piping | Rev |________|________|_______|____
___________|______________|_____|________|________|_______| 20
___________|Non-Conveyor | Prep |________|________|_______|____
___________|Non-Conveyor | Prep |________|________|_______|____
___________|Non-Conveyor | Prep |________|________|_______|____
___________|Non-Conveyor | Prep |________|________|_______|____
___________|____________|_____|________|________|_______| 15

how do I get the location of the 30, 20, 15? sometimes there are more entries so the actual location is based on the number of entries but the value will always be 1 row below the last entry and the 5th column to the left.
I started thinking about using this code

Cells.Find(What:="Conveyor Piping", SearchDirection:=xlNext, LookAt:=xlWhole).Select
            a = Selection.Offset(0, 1).Address
            Cells.Find(What:="Prep", After:=Range("a"), SearchDirection:=xlPrevious).Select

but the problem with that is that when i do the xlPrevious it will go down to the Non-Conveyor and include that. I hope that makes sense

 

[Joe4]

I don't think I understand what you mean, probably because you haven't explained what that other code you have below the stuff we were working on is supposed to do.

Can we take a step back here and look at the bigger picture?
Can you explain exactly what you are trying to do with your macro (what is the end goal here)?

 

[sweetness34]

I'm taking data from a text file then I paste it into a spreadsheet. I then want to run the macro and it will give me a breakdown of hours(thats what the 30, 20, 15 numbers were). The data from the txt file is always the same format but varies greatly in length. So I cant just link cells to cells because, after the macro is run, textfile1.txt will have the hours in (example) cell H10 whereas textfile2.txt will have the hours in (again example) cell H45.

 

[Joe4]

So are you just trying to delete all rows that don't have an hours value in column H?

 

[sweetness34]

No im trying to find that hour value(in column H) and then copy it and paste it in another tab. so far I have around 18 pages of code doing other formatting/deleting etc..
but i need to be able to find the last "Prep" entry of the "Conveyor" entries. If I simply do it for the last "Prep" entry of the spreadsheet it will include "Non-Conveyor"