0

I have tried using Goto on error, but it seems to skip this even when an error occurs, or do it even though an error has not occurred depending where I place it in the script.

The runtime error occurred in this line of code: 

Range(Worksheets("Search Engine").Cells(9, 1), Worksheets("Search Engine").Cells(Endcolumn, Endrow + 2)).Select Selection.RowHeight = 20` when `Endcolumn = 0
Community
  • 1
  • 1
user1545643
  • 313
  • 1
  • 4
  • 10
  • What do you call "an error" ? – ApplePie Jul 23 '12 at 11:13
  • Can you provide sample code? Well, to my knowledge I suppose that it can't skip the error, because in that case it would bug... (?) – html_programmer Jul 23 '12 at 11:33
  • An error as in a runtime error, I've made a search engine which will go through lists of data and transfer any correlating data into the search engine sheet, obviously the data in the sheet shouldnt be changable by the user, but if a runtime error occurs during the search, it will be changable. I have a section that was giving me a rubtime error, but I've fixed that now, but for an example I'll put it up – user1545643 Jul 23 '12 at 11:53
  • This was kicking up a fuss when endcolumn = 0, but I have no prevented that from occuring. Is there a particular place within the code that on error goto should be placed? Range(Worksheets("Search Engine").Cells(9, 1), Worksheets("Search Engine").Cells(Endcolumn, Endrow + 2)).Select Selection.RowHeight = 20 – user1545643 Jul 23 '12 at 11:56
  • 1
    @user1545643: welcome to Stackoverflow, please use the edit link at the bottom of your question to elaborate your question and make it more understandable. You can for instance move in the question body the comments you left above. You will then have more chance to get an answer (the better the question, the better the answer) – JMax Jul 23 '12 at 12:22
  • @user1545643 so if I read your last comment correctly, I am inferring that you have a `Function` or `Sub` that throws an `Application-defined or object-defined error` whenever the `Endcolumn` variable is 0? – psubsee2003 Jul 23 '12 at 12:30
  • Correct it did, I want the program to protect the pages if any runtime error occurs though. Incase they can put something into the search I have not forseen to cause a problem and then be able to edit the sheet due to it being left unprotected when the macro is interrupted by the runtime error. I hope that makes sense, I'm pretty bad a wording things. Thanks – user1545643 Jul 23 '12 at 12:43
  • You logically need to put the "On Error" statement depending on the place from where you want the error handler to be active. For more info on error handling, check out this page: http://www.cpearson.com/excel/errorhandling.htm – html_programmer Jul 23 '12 at 12:51

1 Answers1

4

Based on your comment, you are seeing an Application-defined or object-defined error when your Endcolumn variable is 0. This is happening because the Excel Range is 1-based, not 0-based, which means there is never a column 0.

Since you seem to be most interested in the error handling specifically, here's roughly how it should be handled:

Sub ErrorExample()

    On Error GoTo ErrHandler ' Set the Error Handling Condition
                             '  in this case, if an error occurs
                             '  goto the ErrHandler label

    ' do stuff
    Debug.Print "I am in `do stuff` code"

    Range(Worksheets("Search Engine").Cells(9, 1), 
           Worksheets("Search Engine").Cells(Endcolumn, 
           Endrow + 2)).Select Selection.RowHeight = 20

    Exit Sub ' Exit from the Sub gracefully and do not run the
             '  following lines of code (if this is not
             '  included, the ErrHandler code will run in all
             '  cases, not just error cases

    Debug.Print "I will never run"

ErrHandler:
    Debug.Print "I am in the error code"
    ' Code to run in case of error
    ThisWorkbook.Worksheets("Search Engine").Protect ' protect your sheet
    On Error GoTo 0 ' Reset the error handling condition
End Sub
psubsee2003
  • 8,563
  • 8
  • 61
  • 79