VBA On Error Goto Only Runs Once

[ian.thomas17]

Can anyone tell me why the On Error GoTo statement only executes the first time an error occurs in the following code:

On Error GoTo CubeNotFound
For x = 2 To TotalRecords

Cells.Find(What:=WSID, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate

'Other actions are located here, but have been removed. These actions take place if the Find is succesful.

CubeNotFound:
Message = MsgBox("WS ID " & WSID & " was not found! Excel will continue with the next WS ID.", vbOKOnly, "WS ID NOT FOUND")

Next x

I have that statement there because if WS ID is not found then VB generates an error that the user won't be able to interpret, and also stops the code. I want VB to continue to the next record if one is not found.

 

[Scott Huish]

Instead of using On Error GoTo, try using a range object with Find and then checking to see if it returned anything. I'm not sure why you are using a For Next Loop around it though, and did you really want to check the whole sheet for the Value, this can be restrained to a certain range if you want.

But something like this:

Dim c As Range
Set c = Cells.Find(What:=WSID, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then
    'Your Code Here for if it finds it
End If

 

[Oorang]

The code posted by HOTPEPPER will work. But to answer your question, it only works once becasue you are not clearing the error with err.clear or a Resume statement. The error handler only responds to one error at a time.

 

[ian.thomas17]

OK; thanks. I tried setting up the code suggested by HOTPEPPER but it still generated an error that "Object variable or With block variable not set."

I'll insert a Resume Next statement in my code as the quickfix for this.

To answer HOTPEPPER's other questions:

Yep, I do need to search the entire sheet, but thank you for the suggestion

And the For Next loop is in place because I am searching for the value of field1 in a recordset in another sheet. Once the actions for field1 are done, it goes to field2, and so on.

Thanks everyone!

 

[ian.thomas17]

Still having trouble...

All right; neither resume next or err.clear worked. I'm still getting the "Object variable or With block variable not set."

 

[Zack Barresse]

Can you post your entire code Ian?

 

[ian.thomas17]

All right, guys, you asked for it

Dim TotalRecords As Long 'Stores the total number of records from Data.
    Dim x As Integer 'Used as the For-Next variable.
    Dim WSLocation As String 'Stores the address of the current WS ID.
    Dim WSID As String 'Stores the current WS ID to look for.
    Dim Message As String 'Used for displaying error messages.
    Dim CurCell As String 'Holds the address of the current cell to work with.
    
    'Counts the total number of records and stores it to TotalRecords.
    'Also sets the format of the Columns so that the formulas used later will work properly.
        Sheets("Data").Select
        TotalRecords = Application.WorksheetFunction.CountA(Range("A:A"))
        Columns("B:J").Select
        Selection.NumberFormat = "General"
    
    'Finds each WS ID in Floor Map and sets the fields to the values for the WS ID.
        For x = 2 To TotalRecords
            On Error GoTo CubeNotFound 'Goes to displaying an error message if a WS ID is not found.
            'Finds WSID address.
                WSID = Sheets("Data").Range("A" & x)
                Sheets("Floor Map").Select
                Range("A1").Select
                Cells.Find(What:=WSID, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
                    xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
                    , SearchFormat:=False).Activate
                WSLocation = ActiveCell.Address
            'Sets field values in the Data sheet.
                Sheets("Data").Range("B" & x) = "=offset('Floor Map'!" & WSLocation & ",4,3)"
                Sheets("Data").Range("C" & x) = "=offset('Floor Map'!" & WSLocation & ",5,3)"
                Sheets("Data").Range("D" & x) = "=offset('Floor Map'!" & WSLocation & ",0,3)"
                Sheets("Data").Range("E" & x) = "=offset('Floor Map'!" & WSLocation & ",5,0)"
                Sheets("Data").Range("F" & x) = "=offset('Floor Map'!" & WSLocation & ",4,0)"
                Sheets("Data").Range("G" & x) = "=offset('Floor Map'!" & WSLocation & ",2,0)"
                Sheets("Data").Range("H" & x) = "=offset('Floor Map'!" & WSLocation & ",2,3)"
                Sheets("Data").Range("I" & x) = "=offset('Floor Map'!" & WSLocation & ",1,0)"
                Sheets("Data").Range("J" & x) = "=offset('Floor Map'!" & WSLocation & ",3,0)"
                Sheets("Data").Select

            GoTo NextX 'Skips the error message set up to pop up if the WS ID is not found.
            
CubeNotFound: 'Displays this message if the WS ID is not found and then goes to the next WS ID.
            Message = MsgBox("WS ID " & WSID & " was not found! Excel will continue with the next WS ID.", vbOKOnly, "WS ID NOT FOUND")
            Err.Clear
NextX:
            
        Next x
        
        'Copies the formulas and pastes as values.
            Columns("B:J").Select
            Selection.Copy
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False

 

[Zack Barresse]

Without having direct data to test this on yet, try the following which does not select or activate anything...



<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> IanTestAtMrE()
    <SPAN style="color:#00007F">Dim</SPAN> rngFind <SPAN style="color:#00007F">As</SPAN> Range, wsData <SPAN style="color:#00007F">As</SPAN> Worksheet, wsFloor <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> TotalRecords <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> strLoc <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> wsFloor = Sheets("Floor Map")
    <SPAN style="color:#00007F">Set</SPAN> wsData = Sheets("Data")
    TotalRecords = Application.WorksheetFunction.CountA(wsData.Range("A:A"))
    wsData.Columns("B:J").NumberFormat = "General"
    For x = 2 <SPAN style="color:#00007F">To</SPAN> TotalRecords
        <SPAN style="color:#00007F">Set</SPAN> rngFind = wsFloor.Cells.Find(What:=wsData.Range("A" & x), After:=wsFloor.Range("A" & x))
        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> rngFind <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
            strLoc = rngFind.Address
            wsData.Cells(x, "B").Formula = "=OFFSET('Floor Map'!" & strLoc & ",4,3)"
            wsData.Cells(x, "C").Formula = "=OFFSET('Floor Map'!" & strLoc & ",5,3)"
            wsData.Cells(x, "D").Formula = "=OFFSET('Floor Map'!" & strLoc & ",0,3)"
            wsData.Cells(x, "E").Formula = "=OFFSET('Floor Map'!" & strLoc & ",5,0)"
            wsData.Cells(x, "F").Formula = "=OFFSET('Floor Map'!" & strLoc & ",4,0)"
            wsData.Cells(x, "G").Formula = "=OFFSET('Floor Map'!" & strLoc & ",2,0)"
            wsData.Cells(x, "H").Formula = "=OFFSET('Floor Map'!" & strLoc & ",2,3)"
            wsData.Cells(x, "I").Formula = "=OFFSET('Floor Map'!" & strLoc & ",1,0)"
            wsData.Cells(x, "J").Formula = "=OFFSET('Floor Map'!" & strLoc & ",3,0)"
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> x
    wsData.Columns("B:J").Value = wsData.Columns("B:J").Value
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>



Let us know how it works.

 

[ian.thomas17]

This is fantastic! You have no idea how much I just learned about VB from looking at your code, firefytr! I only needed to add one thing, and that was a "LookAt:=xlWhole" in the search code.

Thanks very much!

 

[Zack Barresse]

You're very welcome my friend. Glad to help.