On Error Problems getting to work

[mcnealfbm]

I saw in an earlier post someone had suggested using On Error and I'm trying to apply it to my code.

I have a main sub routine (Route_Create) and two subroutines (Loader and Expiration)

In the Route_Create routine, I want to look in a specific column and see if the word Expire exists, if it does I want it to call the subroutine Expiration. If it does not then I want it to look in the same column and see if the words Add, Change or Replace exist, if it does then I want it to call the subroutine Loader.

The On Error seems like it might work but I'm don't understand the logic of it so I'm not coding it correctly. I thought an if/else would work better but I couldn't figure out that code.

The first part of the code starts after I find the specific column I need to search for Expire, Add, Replace or Change.

ActiveCell.Select
cellname = ActiveCell.Address
ColNumber = ActiveCell.Column
Rowstart = ActiveCell.row
Rowend = ActiveSheet.UsedRange.Rows.Count

If ColNumber > 26 Then x = 2 Else x = 1
ColLetter = Mid(cellname, 2, x)

' This is where I need the code to look for the words and decide if it should call a subroutine or not.

On Error Resume Next
Cells.Find(What:="Expire", After:=range(ColLetter & Rowstart, ColLetter & Rowend), SearchDirection:=xlPrevious).Select
On Error GoTo 0
Call Expiration

On Error Resume Next
Cells.Find(What:="Add", After:=range(ColLetter & Rowstart, ColLetter & Rowend), SearchDirection:=xlPrevious).Select
On Error GoTo 0
Call Loader

On Error Resume Next
Cells.Find(What:="Replace", After:=range(ColLetter & Rowstart, ColLetter & Rowend), SearchDirection:=xlPrevious).Select
On Error GoTo 0
Call Loader

Thanks for your help!
Frances

 

[VoG]

This is a much better approach since the error can be anticipated

Dim Found As Range
Set Found = Cells.Find(What:="Expire", After:=Range(ColLetter & Rowstart, ColLetter & Rowend), SearchDirection:=xlPrevious)
If Found Is Nothing Then Exit Sub ' or other action

 

[mcnealfbm]

So I create a subroutine to search for Expire and if it found it then to call the expiration subroutine, otherwise end sub which would take it back to the main routine. But when I was stepping through the code I got a Run Time Error 13 data type mismatch when it hit the Set Found = cell....section.

Sub Expiration()
Dim ColLetter As String
Dim cellname As String
Dim ColNumber As Long
Dim Rowstart As Long
Dim Rowend As Long
Dim Found As range

ActiveCell.Select
cellname = ActiveCell.Address
ColNumber = ActiveCell.Column
Rowstart = ActiveCell.row
Rowend = ActiveSheet.UsedRange.Rows.Count

If ColNumber > 26 Then x = 2 Else x = 1
ColLetter = Mid(cellname, 2, x)

Set Found = Cells.Find(What:="Expire", After:=range(ColLetter & Rowstart, ColLetter & Rowend), SearchDirection:=xlPrevious)
If Found Is Nothing Then Exit Sub
Call Route_Expiration

End Sub

Two more questions came up, if I call one subroutine from another with the variables go with it. For example below I have ColLetter, cellname, etc all defined in the main routine, will they be recognized in the subroutine? I didn't think it would.

Last question, how do I write the code when I want it to look for more than one match. I need it to call the Loader subroutine if it finds the words Add or Replace.

Thanks again!

 

[VoG]

1) you need to declare the variables outside the subs, at the top of the module.

2. You will need two tests.

 

[mcnealfbm]

Thanks for the super fast responses!

Any ideas why I get a Run Time Error 13 data type mismatch when it gets to the code:
Set Found = Cells.Find(What:="Expire", After:=range(ColLetter & Rowstart, ColLetter & Rowend), SearchDirection:=xlPrevious)

Frances

 

[shg]

From Help:

<TABLE><TBODY><TR><TD class=mainsection>After</TD><TD class=mainsection>Optional</TD><TD class=mainsection>Variant</TD><TD class=mainsection>The cell after which you want the search to begin. This corresponds to the position of the active cell when a search is done from the user interface. Note that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn’t searched until the method wraps back around to this cell. If you don’t specify this argument, the search starts after the cell in the upper-left corner of the range.</TD></TR></TBODY></TABLE>

 

[mcnealfbm]

I tried for quite a while to get the cells.find to work, but couldn't. While searching through the boards I found something similar where Lbound was used instead but I can't seem to get it to work either. I tried the following.
Dim i As Long
Dim ExpireWords, AddWords
ExpireWords = Array("Expire", "Remove", "Delete")
AddWords = Array("Add", "New", "Replace", "Change")
i = LBound(ExpireWords)
With range(ColLetter & Rowstart, ColLetter & Rowend)
Set Found = .Find(What:=ExpireWords(i), LookIn:=xlValues, LookAt:=xlWhole)
If Found Is Nothing Then range("A1").Select Else: Call Route_Expiration
i = LBound(AddWords)
Set Found = .Find(What:=AddWords(i), LookIn:=xlValues, LookAt:=xlWhole)
If Found Is Nothing Then range("A1").Select Else: Call Create_Route_Loader
End With

It all works perfectly as long as it finds the first word of the array but it won't work for any of the other words in the array. My issue is that once I find a match in the array of any or all of the words, then I only want to call the subroutine one time, it can't keep running the subroutine. So if I have a file with Expire and Remove I only want it to call Route_Expiration one time. What am I doing wrong?

I thought I had all the bugs worked out until I tried to run it on a file with the words New and Remove in my search column.

Frances

 

[Norie]

Frances

What column do you want to search?

Do you only want to search part of the column or is it the whole column?

Will any of the words being looked for repeat?

eg Add will appear 10 times

If that does happen to you need to take appropriate action based on where each instance is found.

For example if Add is found in row 103 then you need to do something with that row.

 

[shg]

Maybe ...

    Dim vsWord      As Variant
    Dim rFind       As Range
 
    With Range(ColLetter & Rowstart, ColLetter & Rowend)
        For Each vsWord In Array("Expire", "Remove", "Delete")
            Set rFind = .Find(What:=vsWord, LookIn:=xlValues, LookAt:=xlWhole)
            If Not rFind Is Nothing Then
                Route_Expiration
                Exit For
            End If
        Next vsWord
 
        For Each vsWord In Array("Add", "New", "Replace", "Change")
            Set rFind = .Find(What:=vsWord, LookIn:=xlValues, LookAt:=xlWhole)
            If Not rFind Is Nothing Then
                Create_Route_Loader
                Exit For
            End If
        Next vsWord
    End With

 

[mcnealfbm]

Perfect, that is exactly what I needed to make it work!

Thanks for the help!

Frances