Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 19

Thread: Force VBA to ignore Runtime Error 1004

  1. #1
    Join Date
    30th May 2011
    Location
    CH
    Posts
    283

    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?

    VB:
    With StrAnalPMain.Worksheets("StrAnalP") 
         
        .Activate 
         
        On Error Resume Next [COLOR=green] 'Doesn't have any effect[/COLOR]
        [COLOR=#008000][/COLOR] 
        .Range("OEWahl").Value = Worksheets("Basisdaten").Range("B" & _ 
        WorksheetFunction.Match(User.[COLOR=blue]fProfitCenter[/COLOR], Worksheets"Basisdaten").Range("C:C"), 0)).Value 
         
        On Error Goto Err_Handler 
         
    End With 
    
    
    Thanks in advance for any insight

    Cheers

    Attila

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,323

    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.
    MS MVP - Excel

  3. #3
    Join Date
    30th May 2011
    Location
    CH
    Posts
    283

    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 at 23:41. Reason: typo

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    23rd October 2003
    Location
    France Alsace
    Posts
    3,697

    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

  5. #5
    Join Date
    30th May 2011
    Location
    CH
    Posts
    283

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    14th July 2004
    Posts
    10,541

    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.
    VB:
    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.
    VB:
     
    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!

  7. #7
    Join Date
    30th May 2011
    Location
    CH
    Posts
    283

    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:

    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    14th July 2004
    Posts
    10,541

    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.

    VB:
    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!

  9. #9
    Join Date
    30th May 2011
    Location
    CH
    Posts
    283

    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

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    14th July 2004
    Posts
    10,541

    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!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 2
    Last Post: June 25th, 2011, 07:01
  2. Runtime error 1004?!??
    By rpaulson in forum EXCEL HELP
    Replies: 3
    Last Post: December 15th, 2005, 00:21
  3. Runtime Error 1004
    By mini12 in forum EXCEL HELP
    Replies: 3
    Last Post: August 10th, 2005, 06:55
  4. RunTime Error '1004'
    By simpsonc2 in forum EXCEL HELP
    Replies: 6
    Last Post: September 8th, 2004, 22:49

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno