Method 'Range' of object '_Worksheet' failed


  • Code Snippet


    DIM LS as Worksheet

    LSTemplate.Visible = True

    LSTemplate.Copy after:=LSTemplate.Parent.Worksheets(LSTemplate.Parent.Worksheets.Count)

    Set LS = LSTemplate.Parent.Worksheets(LSTemplate.Parent.Worksheets.Count)

    LS.Name = BPName

    LS.Range("Position").Value = Trim(gBZA.Range("DBText").Item(BPIndex))


    we have an application , which workes since 5 years now.
    it'a an excel addin .xla file,
    we never had problem with the code in it.

    but now a customer receives the error msg "Error 1004: Application defined or object defined error"

    win xp sp2 , office 2007 sp1

    the code is above 

    problem is , ls.range produces error : "Method 'Range' of object '_Worksheet' failed"

    im very confused -.-

    why this part of code is working everywhere,
    but suddenly it gives such an error ?!?!?

    thx 4 any help in advanced

    Thursday, June 12, 2008 1:18 PM

All replies

  • Hi,

    Have you checke the value of the variables and objects when the code fails?

    What value does BPIndex contain?
    Thursday, June 12, 2008 3:12 PM
  • Code Snippet

    LS.Range("Position").Value = Trim(gBZA.Range("DBText").Item(BPIndex))

    the right part is a string, checked in the vba immediate window, it's k
    the left part is the problem , since ls.range gives the method range of object _worksheet failed error

    what i don't understand, this problem only occurs on one PC ...

    and ls is defined as worksheet, copied from a .xlt file

    i simply run out of ideas what else i should check
    Friday, June 13, 2008 8:56 AM
  • So the range "Position" is causing the problem.

    You need to investigate what the left part references on the offending pc.
    Is the definition of Position resolved correctly?

    If still nothing obvious you need to investigate the pc's setup. Excel version, regional settings etc.
    Friday, June 13, 2008 9:31 AM