errors

[Luis939]

I was just curious, is there a function which detects errors, meaning, lets say i have a regular runtime error, can i create a MsgBox that will bypass the usual error box. Becuase lets say i am certain i know what would cause an error in a certain part of the code, I just to be able to have a msgbox sayin, "woops your error was caused because of such and such" and then have the program end there, or does VBA have to put such an error box out there to see, i just dont want any error messages to pop up, thanks!

 

[uberskunk]

Hello again,

This code should do what you need.

Sub main()

On Error GoTo ErrH

'This will cause the error handler to cut in
Err.Raise 123

Exit Sub


ErrH:

'You can put any error number that you want to capture here
Select Case Err.Number
Case 123
MsgBox ("You mistyped your own name&quot
Case 456
MsgBox ("You should go home&quot
End Select

End Sub

 

[Luis939]

ohhh interesting, i defintely want to check this out, i havent had the chance to use a case statement yet with VBA, i think ill like it lol, thanks!

 

[Luis939]

so i can put this on any part of my code, because the big problem i am having, is including the find method in my spreadsheets, i want to find a particular string, ill use
Cells.Find(What:="wordtofind", After:=ActiveCell, LookAt:=xlWhole, SearchOrder:=xlByColumns).Activate
and if that word is not in my spreadsheet, ill get that pesky error box i dotn like,
so can i put your code right after that cells.find instruction and it will work, how could i incorporate your code with mine, thanks!

 

[uberskunk]

Yeh this should do it.
Just remember to but the 'On Error GoTo ErrH' before the statement that will throw up the error.

Alternatly you could just put it as the first line of code you run in the sub.


Sub main()

On Error GoTo ErrH

Cells.Find (<insert find code here>)

Exit Sub

ErrH:

Select Case Err.Number
Case 123
MsgBox &quot;Blah, blah&quot;
Case 456
MsgBox &quot;Blah, blah, blah&quot;
Case Else
MsgBox &quot;No Error, but I thought I'd let you know&quot;
End Select

End Sub




Mike

 

[Luis939]

yea thats what i was doing right now thanks, but let me ask you this about what im doin, im basically taking one cell value from one file, and i want to see if that same cell value is in another worksheet-in another workbook, the method i use is i copy the cell from one file and paste it in cell A1 in the other file, then i execute that find method command and thats how i would sometimes get that error box becuase a match was not found, do you feel that is the best way of doing what i want, is that slower or less effecient than another way, is there a way of not having to do all that and just sayin take the activecell and see if its anywhere in that other file without having do a copy and paste, thanks man, your such a great help!

 

[nath]

Hi,

You should always limit the interface with the spreadsheet (copy and paste business) when possible as it is more time consuming. You could do something like:

Sub findMyCell
  Dim rCell as Range
  dim str as String

  str = ThisWorkbook.Sheets("ThisSheet").Range("MyNameOrMyAddress").Value

 set rCell = Workbooks("OtherWB").Sheets("OtherSheet").Range("FindRange").find (blabla parameters)

if rCell is Nothing then
   msgbox str & "Doesn't exist"
else
   msgbox str " exists in row " & rCell.Row & " and column " & rCell.Column
end if

Nathalie

 

[uberskunk]

Firstly my appologies for not giving you the full code here. But I'm affraid I most go for the day.

But basically you will want to use

dim sFind as string
dim found as boolean

sFind = ActiveCell.Value

do until found or i = Workbooks.count
Workbooks(i).activate 'I'm not expecting this line to work exactly correct

Cells.Find(<Find code>)
if find = true then
found = true
endif

i = i + 1
loop


The only problem with this code sample is it doesn't account for multiple occurences of the search string. So if the same string exists in 3 different workbooks then you will have to rethink the code here.

To do this you could try creating a public variable of the position to start the find from.


Hope this helps, I will check back tomorrow.
Mike

 

[Luis939]

ok ill take a look at your code, thanks!

 

[Luis939]

somethings not goin right at all with my error handler, i have this code

On Error GoTo ErrHandler

Sheets(&quot;Sheet1&quot.Select
Err.Raise 1
and then i have the case statements

so I test it by mispelling sheet1, i put shet1, and it would bypass the error message, BUT, even if i do spell it right, it will still read the Err.Raise 1 line and still go to the error handler, whats goin wrong?? If theres no error, why does it still act like its an error??

 

[Luis939]

ok i see now what i did wrong and how i misunderstood everythign, but as far as the first reply goes, i dont understand why the Err.Raise 1 line was put, because whenever i put that in my code, the flow of control immediately goes to the case statement, no matter where it was found, and i didnt want that, and why is there the statement 'Exit Sub' is that so that when the case statement is done that the flow of control is trasfered back to where the error occured the and sub is exited. I have my case statement at the end of the program, so i guess i woudltn need that exit sub statement huh?

 

[HobbitK]

4335 ...
The line err.raise is used by programmers to test the error handling of the code. basically it is calling the error function and passing it an error number. When you finally compile your code, you will want to remove that line.
As for the other question you asked ...
The Exit Sub is necessary for this reason ..
Lets say your code is running and being executed line by line. The First thing it sees is OnError GoTo and a label.
Then lines of code
.
.
.
.
An error occours here ... the code jumps down to the error label. and runs on from there ..
BUT
If no error occoured, it will run into the Exit Sub line .. without it, the error handler would run everytime.

 

[Luis939]

Yea i actually I didnt realize until late yesterday that I needed to remove that Err.Raise line, i thought it was required. So thanks for the explanation.

 

[Luis939]

Hey, I noticed one big thing when implementing this code, essentially, to the compiler, there is no error anymore, because you are redirecting it to do something else if there is an error, and then ending the sub. Is there anyway that I can still be able to debug it the old way so that the compiler can highlight the line where the error occured, or is that not possible. Because I still wanted that ability to go back and change something in the code if it was a logical error such as mispelling a worksheet name or something like that, thanks!

 

[Luis939]

I tried using Nath's code for my program and everythign worked fine except for the msgbox line

i keep getting errors because of that rcell.row and rcell.column
any idea how i can display the cell id of rcell

 

[Luis939]

can anyone help, now that i can use Nath's code to find rcell, i want to be able to find yet another thing (that i specify-not a cell) and try to find it in the other work book after rcell

i already have
Set rCell = Workbooks(&quot;NortheastArea2003.xls&quot.Sheets(g).Range(&quot;B:B&quot.Find(what:=ActiveCell, lookat:=xlPart, searchorder:=xlColumns)

now i want to be able to find &quot;AT&T&quot; in NortheastArea2003 after the cell that rCell found. Can anyone help?

 

[nath]

Hi,

You should be able to use:

Set rCell = Workbooks("NortheastArea2003.xls").Sheets(g).Range("B:B").Find(what:="AT&T",after:= rCell, lookat:=xlPart, searchorder:=xlColumns)

Or is there a problem?

Nath

 

[Luis939]

yea thanks, i was able to figure out putting after:=rcell as one of the parameters, but it took me a while, thanks for getting back to me though, it's greatly appreciated!