Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    error trapping (2003)

    I am using the worksheet function:

    stringProjectCostString = Application.WorksheetFunction.VLookup(longProjectZ ooCode, Range("'Accounts output'!A:I"), 9, False)

    Normally the function works well, but I cannot always guarantee that there will always be a value to return.

    Can someone please assist me with writing an error handler as the normal on error next type of function does not seem to work when the above returns N/a.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: error trapping (2003)

    <pre> On Error Resume Next
    </pre>


    works fine for me in XL2K. What happens for you?
    Legare Coleman

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: error trapping (2003)

    To expand on what Legare wrote:

    You need to set the value to Null, then ignore errors, then run it then check for the null, something like this:

    stringProjectCostString = ""
    On Error Resume Next
    stringProjectCostString = Application.WorksheetFunction. _
    VLookup(longProjectZooCode, Range("'Accounts output'!A:I"), 9, False)
    On Error GoTo 0
    If stringProjectCostString ="" Then
    'Code for "not found"
    else
    'Code for "found"
    end if

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: error trapping (2003)

    For inofrmation, I get the following message

    Unable to get the vlookup property form the worksheet function

    Please advise, where should I put the error statement for best effect

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: error trapping (2003)

    Have you tried Steve's code?

    Although <code>Range("'Accounts output'!A:I")</code> should work, you can try replacing it with <code>Worksheets("Accounts output").Range("A:I")</code>

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: error trapping (2003)

    I am now baffled. We have 2 versions of this program, one for data in respect of single projects and the second for multiple projects. I have included the code provided inside both versions and give copies of the code below. The single version project ocde works. The multiple version returns the following error:

    Unable to match property of the worksheet function.

    I set out below the relevant code.

    Single project which Works
    ****************************
    Sub CalculateProjectCosts()
    Dim ProjectCostString As String
    Dim updateWithLastMonthFigures As Boolean
    Dim intUpdate As Integer
    Dim MissingCodes As String
    Dim CodeFound As Long
    On Error Resume Next
    CodeFound = 0
    intUpdate = MsgBox("Update with last month's figures?", vbYesNo)
    If intUpdate = 6 Then
    updateWithLastMonthFigures = True
    Else
    updateWithLastMonthFigures = False
    End If
    ThisWorkbook.Worksheets("Project Costings Calculator").Activate
    CodeFound = WorksheetFunction.Match(Range("'Project Costings Calculator'!$C$7").Value, Range("'Accounts output'!A:A"), 0)
    On Error GoTo 0
    If CodeFound > 0 Then
    Call CalculateProjectCostsDetail(Range("$C$7"), updateWithLastMonthFigures)
    Call ModWorkStreamDetailCalc.ProjectWorkStreamDetailCal c
    MsgBox ("Im done")
    Else
    MissingCodes = Range("'Project Costings Calculator'!$C$7").Value
    MsgBox ("Finished No Processing performed" & vbCr & "Missing Code:" & MissingCodes)
    End If
    End Sub

    Multiple version which fails:
    ******************************
    Sub SaveMultipleProjects()
    Dim i As Integer
    i = 3
    Dim ProjectZ00Code As Long
    Dim updateWithLastMonthFigures As Boolean
    Dim intUpdate As Integer
    Dim MissingCodes As String
    Dim CodeFound As Long
    On Error Resume Next

    MissingCodes = ""
    intUpdate = MsgBox("Update with last month's figures?", vbYesNo)
    If intUpdate = 6 Then
    updateWithLastMonthFigures = True
    Else
    updateWithLastMonthFigures = False
    End If

    While ThisWorkbook.Worksheets("Save Multiple Projects").Cells(i, 1).Value <> ""
    CodeFound = 0
    ProjectZ00Code = ThisWorkbook.Worksheets("Save Multiple Projects").Cells(i, 1).Value
    CodeFound = WorksheetFunction.Match(ProjectZ00Code, Range("'Accounts output'!A:A"), 0)'****Fails here if code does not exist*****
    On Error GoTo 0
    If CodeFound > 0 Then
    ModCalculateProjectCosts.CalculateProjectCostsDeta il ProjectZ00Code, updateWithLastMonthFigures
    ModWorkStreamDetailCalc.ProjectWorkStreamDetailCal c
    ModSaveProjectFiles.SaveCurrentProjectFile
    Else
    MissingCodes = MissingCodes & Str(ProjectZ00Code) & vbCr
    End If
    i = i + 1
    Wend

    MsgBox ("Im done" & vbCr & "The following codes were missing" & vbCr & "from the Accounts Output sheet:" & vbCr & MissingCodes)

    End Sub

    **************
    End of code

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: error trapping (2003)

    The placement of On Error Resume Next is wrong. It should be like this:

    On Error Resume Next
    CodeFound = WorksheetFunction.Match(ProjectZ00Code, Range("'Accounts output'!A:A"), 0)
    On Error GoTo 0

    so that On Error Resume Next is set for each pass through the loop.

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: error trapping (2003)

    Thanks

    That works. For my information, is the on error code more like setting a variable rather than implementing an instruction?

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: error trapping (2003)

    The "on error" is more like a "set of the code"

    Once you tell VB what to do with an error it will persist until the routine stops.

    "On error resume next" tells VB to ignore all errors and just proceed. "On error goto 0" resets error handling to normal.

    So the procedure is in this case to give a value to the variable, ignore errors, run the vlookup, (the value will not be changed if no match is found, so it is essential to have a "default value" put into it), then reset the error handling

    Steve

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: error trapping (2003)

    Perhaps not widely known. When using the worksheet functions from VBA, the internals differ when using a different syntax.

    If we use Application.WorksheetFunction.Vlookup, an error caused by the worksheet function raises a runtime error.

    We can however also use Application.VLookup directly. Then the function returns an error result rather than causing a runtime error. See this code example:

    <pre>Sub test()
    Dim longProjectZooCode As Long
    Dim stringProjectCostString As Variant
    longProjectZooCode = 1
    stringProjectCostString = ""
    ' On Error Resume Next
    stringProjectCostString = Application. _
    VLookup(longProjectZooCode, Range("'Accounts output'!A:I"), 9, False)
    On Error GoTo 0
    If TypeName(stringProjectCostString) = "Error" Then
    'Code for "not found"
    MsgBox CStr(stringProjectCostString)
    Else
    MsgBox stringProjectCostString 'Code for "found"
    End If

    End Sub</pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: error trapping (2003)

    Thanks, I didn't know that! Is it an undocumented feature? VLookup and other worksheet functions are not listed as methods of the Application object in the online help or in the Object Browser.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: error trapping (2003)

    I did not know this (obviously).

    Yes, this method eliminates the need for On error resume next

    I know you are aware of this, but for others, it is critical that that variable to hold the "lookup value" (from v or hlookup or match, etc) is a variant. since it must be able to "contain" the error...

    Steve

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: error trapping (2003)

    The WorksheetFunction collection (or is it an Object?) was introduced with Excel 97, before that you didn't need it, you just preceded a function with Application. It is still possible that way for backward compatibilty reasons. With a slight difference in error handling as you saw.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  14. #14
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: error trapping (2003)

    Jan Karel

    Please find attached a s/s in which I have tried to use the code from your post above.

    The code should be subject to the error trap when i attains a value of 4. In fact when I add a watch to cells(i,2).value at i = 4, I can see that the value associated with this statement is "not known" whereas the value of tempemailaddressholder remains at "alex.pergunas@kpmg.co.uk", the value which it picked up when i was 3.

    Can you please assist in helping me to trap the error which should arise where the Summary sheet tries to pick up details in respect of a Business unit which has not been entered into The finance manager sheet.

    Thanks

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: error trapping (2003)

    Since you want to inspect the TypeName of tempEmailAddressHolder, it should be declared as a Variant, not as a String. If you declare it as a String, the TypeName will always be String. So:

    Dim tempEmailAddressHolder As Variant

Posting Permissions

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