+ Reply to Thread
Results 1 to 6 of 6

application defined or object defined error

Hybrid View

SMazoz application defined or object... 01-17-2010, 05:39 AM
Richard Buttrey Re: application defined or... 01-17-2010, 05:46 AM
SMazoz Re: application defined or... 01-17-2010, 06:58 AM
PMO Re: application defined or... 01-17-2010, 09:53 AM
rwgrietveld Re: application defined or... 01-17-2010, 10:26 AM
SMazoz Re: application defined or... 01-17-2010, 12:48 PM
  1. #1
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: application defined or object defined error

    A few things that I notice

    Range("CompSubBranch") = Empty
    Empty is used as an unitialized vaiable. Just use
    Range("CompSubBranch") = ""
    For a validation (between) I expect a Formula like
    Formula1:="1", Formula2:="3"
    I would not use this : Sheet1.Protect

    Instead
    Dim Ws1 as Worksheet
    
    Set Ws1 = Worksheets("Sheet1")
    Ws1.Protect
    why have
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
    When the strings are "" (empty) ?

    I understand that your rng goes upto Q200, but where it could go wrong:
    Function GetRange(val As Integer) As String
       Dim startPnt As Integer 
       Dim endPnt As Integer
       For Each Item In Range("'List'!Q2:Q200")
        If Item.Value = val And startPnt = Empty Then startPnt = Item.Row
        If Item.Value = (val + 1) And endPnt = Empty Or Item.Value = Empty And endPnt =Empty Then endPnt = Item.Row - 1
      Next
       GetRange = "$P" & startPnt & ":$P" & endPnt
    End Function
    Use startPnt = "" and
    An integer can take values up to 32,767.

    startPnt = Item.Row, so theoretically it can become as large as Cells.rows.Count, which is already 65,536 in XL2003.
    This will give an overflow error '6'

    Where it goes wrong (I think) is here Range("'List'!Q2:Q200")
       Set WsList = Worksheets("List")
       For Each Item In WsList.Range("Q2:Q200")
    Last edited by rwgrietveld; 01-17-2010 at 10:52 AM.
    Looking for great solutions but hate waiting?
    Seach this Forum through Google

    www.Google.com
    (e.g. +multiple +IF site:excelforum.com/excel-general/ )

    www.Google.com
    (e.g. +fill +combobox site:excelforum.com/excel-programming/ )

    Ave,
    Ricardo

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1