Results 1 to 3 of 3

Thread: Error Handling When Range Is Not Defined Properly

  1. #1
    Junior Member
    Join Date
    Sep 2012
    Posts
    8
    Rep Power
    0

    Error Handling When Range Is Not Defined Properly

    I have a macro with a vlookup statement and I know that from time to time the macro is going to bomb because I know that my users will not always correctly update the range the lookup statement refers to. When the program bombs, I want there to just be either a blank cell or a zero and ANYTHING other than a number populated in the targeted cell where my result goes. I've tried using "On Error Resume Next" in my code. This prevents my macro from crashing, but for some reason, the last legitimate answer from the previous row is entered in the row where it should bomb. So for example, the cell of the previous row shows $50 which is correct, but then in the next row where it should show nothing or a zero, it too shows $50.

    The relevant code is as follows:
    Code:
     
    For counter = custrow To finalrowbilling
    custname = Worksheets("billing").Cells(custrow, custcol)
    classname = Worksheets("billing").Cells(custrow, classcol)
    On Error Resume Next
    Price = Application.WorksheetFunction.VLookup(custname, bk, WorksheetFunction.Match(classname, bk2, 0), False)
    Worksheets("Billing").Cells(custrow, finalcolbilling).Offset(0, 1) = Price
    custrow = custrow + 1
    Next counter
    Any help would be greatly appreciated.
    Last edited by Excel Fox; 05-13-2013 at 04:45 PM. Reason: Corrected Code Tag

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Try
    Code:
     For counter = custrow To finalrowbilling
    custname = Worksheets("billing").Cells(custrow, custcol)
    classname = Worksheets("billing").Cells(custrow, classcol)
    On Error Resume Next
    Price = Application.WorksheetFunction.VLookup(custname, bk, WorksheetFunction.Match(classname, bk2, 0), False)
    If Err.Number=0 Then
    Worksheets("Billing").Cells(custrow, finalcolbilling).Offset(0, 1) = Price
    End If
    Price = 0
    Err.Clear:On Error GoTo 0
    custrow = custrow + 1
    Next counter
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Sep 2012
    Posts
    8
    Rep Power
    0
    I hope I am not violating one of your site rules by sending this message, but thanks VERY much. It works beautifully now!!!

Similar Threads

  1. Difference Between 'On Error GoTo 0' And 'On Error GoTo -1'
    By Transformer in forum Familiar with Commands and Formulas
    Replies: 7
    Last Post: 07-02-2015, 04:07 PM
  2. Run Time error '9': Subscript out of range
    By antonio in forum Excel Help
    Replies: 4
    Last Post: 03-26-2013, 01:53 AM
  3. Replies: 6
    Last Post: 12-12-2012, 08:03 PM
  4. Excel Macro to Sort Data if a value changes in defined range
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 4
    Last Post: 09-05-2012, 10:31 AM
  5. Replies: 10
    Last Post: 04-07-2012, 05:33 AM

Posting Permissions

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