Error handling issue

[eawachte]

Line 6 of my code should in this scenario have an error. The number "18" should not be found in the search area and should skip the line of code and go to the Err5: call out several lines down. I have this line of code above and it runs through 4 scenarios just fine (I've also ran this many times with no error) all of a sudden now I am having an issue. Is there some underlying issue here that I am not doing correctly that makes this not robust??

Dim PS5 As String
On Error GoTo Err5:
'Stop Time
Workbooks(Import).Activate
Sheets(2).Activate
ActiveWorkbook.ActiveSheet.Range("G194:G236").Find ("18")
''''%%%'''should have error here if "18" not found

ActiveWorkbook.ActiveSheet.Range("G194:G236").Find("18").Select
PS5 = Selection.Offset(0, 1).Text
Workbooks(DRREPORT).Activate
ActiveWorkbook.ActiveSheet.Range(Home).Select
ActiveCell.Offset(2, 11) = PS5

Err:5

More code

Thanks for any help!!!

 

[eawachte]

OK i will post the answer to my own question so hopefully it will help someone else.

I had something happen in the above circumstance that did not previously occur in my data where multiple errors in the same sub had to be handled. I have...

On Error GoTo 4
''''Code
'Code that caused error'
4

Then it would go to my next section of code

On Error GoTo 5
''Code
'Code that caused error'
5

To handle the second error i needed

On Error GoTo 4
''''Code
'Code that caused error'
4
Resume Next

Then it would go to my next section of code

On Error GoTo 5
''Code
'Code that caused error'
5
Resume Next

I' just an amateur VBA coder, but hopefully what I have posted here is correct. Seems if you plan to handle more than one error potentially you need to have resume next to "reset" the error handler in you next section of code

 

[shg]

Find doesn't error if the value is not found; it returns Nothing. You aren't assigning the result to anything to test:

  Dim r             As Range

  Set r = Range("G194:G236").Find("18")

  If r Is Nothing Then
    MsgBox "Not found"
  Else
    ' do something else
  End If

 

[eawachte]

The above is wrong... still need help

 

[eawachte]

Ok thanks I'll give it a try!

 

[shg]

You're welcome.

 

[eawachte]

I struggle with IF statements at times...

I added what you have to my code, but i keep getting some kind of "End If' Statement error.

I get this a lot actually

I ended up with

If ActiveWorkbook.ActiveSheet.Range("G194:G236").Find("18") Is Nothing Then GoTo 6:
'code
6:

and this seems to work correctly, but i know what you have is the correct way to do it

Why when I add "End If" do i get an error before the code even runs?

 

[shg]

There are two kinds of if statements: in-line:

if a then b [else c]

... and block:

if a then
b
else
c
end if

The block if requires an end if statement.

 

[davesexcel]

Possibly somethi8ng like this,, you need the full names and extensions of the workbooks.

Sub Button1_Click()
    Dim Wb As Workbook, Bk As Workbook
    Dim ws As Worksheet, sh As Worksheet
    Dim rng As Range, x As Range

    Set Wb = Workbooks("Import.xlsx")
    Set Bk = Workbooks("DRREPORT.xlsm")
    Set ws = Wb.Sheets(2)
    Set sh = Bk.Sheets(1)
    Set rng = ws.Range("G194:G236")

    Set x = rng.Find(what:=18, lookat:=xlWhole)

    If Not x Is Nothing Then

        sh.Range("Home").Offset(2, 11) = x.Offset(, 1)

    Else: MsgBox "Not Found"

    End If


End Sub