Announcement

Collapse
No announcement yet.

Force VBA to ignore Runtime Error 1004

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Force VBA to ignore Runtime Error 1004

    Hello everyone

    Here is my first question on the forum:

    How can I override Runtime Error 1004?

    The Error occurs when fProfitCenter can't be found in the worksheet "Basisdaten". If this happens I just want the code to continue as if nothing happened, as it is simply a preference setting in my application. I have tried on Error Resume Next, but that doesn't work - any ideas? Is it even possible?

    Code:
    With StrAnalPMain.Worksheets("StrAnalP")
     
          .Activate
     
                 On Error Resume Next 'Doesn't have any effect
     
          .Range("OEWahl").Value = Worksheets("Basisdaten").Range("B" & _
            WorksheetFunction.Match(User.fProfitCenter, Worksheets"Basisdaten").Range("C:C"), 0)).Value
                
                On Error GoTo Err_Handler
     
    End With
    Thanks in advance for any insight

    Cheers

    Attila

  • #2
    Re: Force VBA to ignore Runtime Error 1004

    Replace WorksheetFunction with Application; it returns a handle-able error. Make sure the VBE is configured to Break on unhandled errors.
    Entia non sunt multiplicanda sine necessitate.

    Comment


    • #3
      Re: Force VBA to ignore Runtime Error 1004

      Thanks shg
      Unfortunately replacing WorksheetFunction with Application causes a type-mismatch error....
      Application.WorksheetFunction causes the same 1004 runtime error.

      Where can I configure that?

      Cheers
      Last edited by Attilas; June 1st, 2011, 22:41. Reason: typo

      Comment


      • #4
        Re: Force VBA to ignore Runtime Error 1004

        Can you send a piece of file to make the real checking
        Triumph without peril brings no glory: Just try

        Comment


        • #5
          Re: Force VBA to ignore Runtime Error 1004

          That would prove to be very difficult.... This is part of an application with 5000 lines of code, lots of company specific (and confidential) data. The code I have written at the top works without a problem so long the user has specified a favorite profitcenter which is available. The only moment I get an error is when this profitcenter is not specified or is not listed in the particular part of the application.

          All that code does is check the User's favorite profitcenter (User.fprofitcenter --> property of custom class), and identifies which key it is associated with so that the user's favorite data is displayed.

          The fact that an error occurs is perfectly understandable - I just need to find a way to trap it. Since Resume Next is not an option, I am at a loss...

          Cheers

          Attila

          Perhaps I should try using another method than worksheetfunction.Match ...
          Thanks for the input. Any further insight is appreciated.

          Comment


          • #6
            Re: Force VBA to ignore Runtime Error 1004

            Whatever method you use you shouldn't ignore the error.

            You can match the error produced by Match, but all you need to change is how you are using whatever result it returns in the code.

            Instead of using it directly in Range(...) you should first assign the result to a variant.
            Code:
            x = Match(User.fProfitCenter, Worksheets"Basisdaten").Range("C:C"), 0)
            Now you can use IsError to check if an error has been returned.

            Mind you I would actually suggest you use VBA Find instead of Match.

            Something like this perhaps.
            Code:
             
            Dim rngFnd As Range
                ' ...
                
                
                Set rngFnd = Worksheets("Basisdaten").Range("C:C").Find(What:=User.fProfitCenter)
                If rngFnd Is Nothing Then
                    MsgBox "Profit center not located."
                    'take further appropriate action if required
                Else
                    StrAnalPMain.Worksheets("StrAnalP").Range("OEWahl").Value = Worksheets("Basisdaten").Range("B" & rngFnd.Row).Value
                End If
                ' ....
            By the way, the With isn't really needed in that code.

            You rarely if ever need to use Activate/Select in Excel VBA and using With to qualify a singe cell/range is perhaps a bit much.
            Boo!

            Comment


            • #7
              Re: Force VBA to ignore Runtime Error 1004

              Thanks Norie that's what I was looking for!

              The thing with Range Find is that I might not get the correct one (The profitcenters are listed up to 3 times in different contexts on the sheet - might not be ideal data structure, but there are enough sheets as it is...)

              PS:the activate is to show the user the main page of my application (This is a snippet of the end of the startup code )

              Here is what it looks like now:

              Code:
              With StrAnalPMain.Worksheets("StrAnalP")
               
                    .Activate
               
                    Rval = Match(User.fProfitCenter, Worksheets"Basisdaten").Range("C:C"), 0)
              
                    If Not IsError(Rval) Then
                         .Range("OEWahl").Value = Worksheets("Basisdaten").Range("B" &  Rval).Value
                    End if
              
              End With
              Cheers

              Attila

              Comment


              • #8
                Re: Force VBA to ignore Runtime Error 1004

                If you meant that it might find partial matches that's easy to deal with using the various arguments/settings of the Find method.

                One of those, I can't recall the name offhand, will ensure only an exact match is searched for.

                By the way I would recommend, whether you use Match or Find, you don't search an entire column.

                Can't you be more specific about that? ie narrow down where to search

                PS If you want to display/move focus to a particular worksheet you do not need to use Activate, in fact it might not work every time.

                Try using Goto instead.

                Code:
                Application.Goto Reference:=StrAnalPMain.Worksheets("StrAnalP").Range("A1"), Scroll:=True
                PS Still not sure why you are using With.

                In this particular code it's use is a little bit confusing, for me anyway.

                It took me a wee while to untangle what the code was meant to do.
                Boo!

                Comment


                • #9
                  Re: Force VBA to ignore Runtime Error 1004

                  Sorry about the with - there is more stuff there than just what I posted for my problem. There is A LOT of code in there and I tried to give just the necessary.
                  You mean MatchCase:= False , yes but I've used the exact Profitcenter 3 times you see.... Narrowing it down to the one column makes sure that it finds the correct key.

                  I'll use a dynamic named range for the search in that case. How many thousandths of a second does that save me xD ?

                  Thanks for the GoTo tip, I'll make sure that I use that instead!

                  Cheers

                  Attila

                  Comment


                  • #10
                    Re: Force VBA to ignore Runtime Error 1004

                    Attila

                    I don't understand, if there are 3 instances of what you want to find how wlll using Match make a difference?

                    If it's because that will always return the first match it encounters, Find's got an argument for that.

                    Well it's actually more than one, basically you can tell Find where to start the search (After), the search order (SearchOrder) etc

                    As for an exact match, I don't mean MatchCase, which I think might look for a match with the same case as the search term.

                    Like I said I can't remember the name of the argument but the constant you use to look for an exact match is xlWhole, found it the argument name is LookAt.

                    I suggested narrowing the search range to avoid the, admittedly small, possibility of getting incorrect results.

                    Might speed things up as well, you never know.
                    Boo!

                    Comment


                    • #11
                      Re: Force VBA to ignore Runtime Error 1004

                      Hi Norie,

                      I presume you closed this thread by accident? I am going to re-open it.
                      Reafidy

                      Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

                      Comment


                      • #12
                        Re: Force VBA to ignore Runtime Error 1004

                        Readify

                        Apologies if I did, I don't know how that happened though.

                        Perhaps I mistakenly tabbed somewhere I never meant to.

                        Actually, I've been having problems posting recently - for some reason the cursor seems to disappear or get 'stuck' when I'm editing.

                        In fact it's just happened a couple of times when writing/editing this post.
                        Boo!

                        Comment


                        • #13
                          Re: Force VBA to ignore Runtime Error 1004

                          There are 3 instances but they are in different columns. Using Match I can specifiy the range.

                          Now you'll tell me that I can do that with find as well - truth is, I have no idea why I went for match, maybe it's because I have to type in less parameters (with find I like being overly exact in what I get....).

                          To be honest I've almost always used range.find until shortly, but I often have to specify a certain offset, so now I started to think that I might as well go for VLOOKUP, MATCH or DGET directly through vba.

                          Comment


                          • #14
                            Re: Force VBA to ignore Runtime Error 1004

                            Well you can specify the range/column with Find too, in fact you need to - Find is a method that applies to a Range.

                            I know the Find part of the code I posted didn't include all the arguments but it did specify to search the same range as
                            you were using with Match.

                            There are quite a few arguments for Find and sometimes all of then aren't always needed.

                            I usually use the macro recorder to get the full syntax for Find, then I change the relevant arguments to what I need and
                            leave the rest well alone.

                            As for the offset thing, you could use Offset with the range reference you'll get if Find locates a match.

                            In fact you can do a lot more than Offset with the range.
                            Boo!

                            Comment


                            • #15
                              Re: Force VBA to ignore Runtime Error 1004

                              Find may be more polyvalent but I am well aware that in this case find and match are interchangeable - Thats the beauty of VB / VBA you can get the same job done with many different (more and less efficient) methods.

                              Find will return a range while Match returns the row (in this case, because the specified range starts with row 1) which I am interested in.
                              I could use rngVar.Offset(0, - 1).Value but I am perfectly happy with Range("B" & row).Value.

                              Regards

                              Attila

                              Comment

                              Working...
                              X