Error 91 when using On Error GoTo. Works first time but not second.

[9tanstaafl9]

Is there some sort of rule against having more than one On Error Goto?

I have two On Error Goto's in my macro, both of which WORK PERFECTLY if the other is commented out, but only the first works if they are not commented. I'm sure I'm missing something really basic but I can't figure it out.

In both cases, I am searching for a trigger in column F, which if found will select a range, then do my code and repeat until the search doesn't any more triggers. I probably could have found a much better way to do this, but I am not very good at coding yet, and the book VBA for dummies isn't helping.

So, if you could either tell me what's wrong with my GoTo code, OR tell me a better way to do what I want to do I would GREATLY appreciate it. I've been trying this on my own since last night.

'Replaces exported totals with formulas. Will temporarily screw up subtotals, but will fix later when we add real formulas for them.
Start:
        Columns("f:f").Select
        On Error GoTo NotDone
        Selection.Find(What:="zz", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Select
        Selection.Value = "uuu"
        If Selection.Offset(2, -1) <> "" Then
        Selection.Offset(1, -1).Select
        Range(Selection, Selection.End(xlDown)).Select
        Else: Selection.Offset(1, -1).Select
        End If

        For Each Cel In Selection
        If Cel.Value <> "" Then Cel.Value = "Your Text" 'need to make Your Text a formula to sum the two cells to the left of each cel, figure out later
        Next

GoTo Start


NotDone:
    
'Finds total fields in Equipment section and adds blank rows after (need for selections later)
StartA:
        Columns("f:f").Select
        On Error GoTo Done
        Selection.Find(What:="xx", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Select
        Selection.Value = "hhh"
    Selection.Offset(1, 0).EntireRow.Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
Done:

 

[Scott Huish]

My piece of advice is to not use On Error Goto. There is another way.

I'll give a little more basic example, since I'm not quite sure what you are trying to do exactly. By the way, there is also no need to do all that Selecting you can work with ranges directly in VBA.

Anyway,
In this example, we are going to look for TEST in column C.

Sub test()
Dim c As Range
Set c = Range("C:C").Find("TEST")
If Not c Is Nothing Then MsgBox "Found It"
End Sub

 

[9tanstaafl9]

I kind of understand. Can you explain to me what the Not c means exactly?

Yes I know about the selections. I go back and get rid of those later. Unfortunately this is still how I think on the first round. (Scary I know.) The point is that they WORK individually, but not if both are available in the macro, and I I can't figure out why. They break on the Selection.FIND part. Sorry I should have mentioned that.

As for what I was trying to do, the exported report I have to deal with has a whole bunch data, with subtotals in col E, but they are not always separated by blank lines. My end goal is to replace the exported subtotals with actual math so the user can later easily insert lines and add data without having to manually adjust the totals. I also need to replace all the numbers that aren't subtotals with math that says basically cell C x D = E. That's really all I want to do. It all actually works separately, but not if both errors are present. I was using the error to so that I could search the entire column for xx and do what I wanted each time. There could be two or a thousand.

Basically my total column E and F look like this:

Col E Col F

total zz
1
2
2
1
6 xx
3
2
5 xx

11 jj

Total zz
4
4
8 xx

8 JJ

Total zz
1 xx
1 jj

Total zz
2
2
2
6 xx

Total zz
1
1
2 xx

8 jj

(Not very logical but I have to deal with it. The original report being exported isn't mine. I did get them to add the letters in column F.)

And there can be any number of subtotals or groups. Some of which have the word total above and some don't. I figured searching for the xx would solve my issue to add the blank lines afterwards (I'd delete them later), and searching for the ZZ before I inserted the lines let me select all the numbers and replace them with math.

 

[9tanstaafl9]

Ok. I replaced the first part of my macro with this:

Dim c As Range
Set c = Range("f:f").Find("zz")
If Not c Is Nothing Then Range(c.Offset(1, -1), c.Offset(1, -1).End(xlDown)).Value = "MyText"

This works to make the first grouping say "MyText" but not any of the others:

Col E Col F

total zz
MyText
MyText
MyText
MyText
MyText xx
MyText
MyText
MyText xx

11 jj

Total zz
4
4
8 xx

8 JJ

Total zz
1 xx
1 jj

Total zz
2
2
2
6 xx

Total zz
1
1
2 xx

8 jj

 

[9tanstaafl9]

NEVERMIND. I figured out a different way to do it. While I am still curious as to why my original macro pieces would work separately but not together, I don't need to know. Now using code I found elsewhere on the site:

<code>
Set rFoundCell = Range("F1")

For lCount = 1 To WorksheetFunction.CountIf(Range("f:f"), "zzz")

Set rFoundCell = Range("F:f").Find(What:="zz", After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

With rFoundCell
.Value = "wow"
End With

Next lCount
</code>

 

[Scott Huish]

The Not c is nothing checks to see if a range was assigned to the range variable. If it was empty, it would be nothing. We want to know if it found it so we use NOT to reverse the TRUE/FALSE.

Not c is Nothing would be TRUE if it found the value, and FALSE if it didn't. Without the NOT it would be the other way around.