Method 'Range' of object '_Worksheet' failed

Question

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))

hello,

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

bernd

Answer 1

Hi,

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

What value does BPIndex contain?

Answer 2

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

Answer 3

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.