runtime error '1004' delete Method of Range Class Failed

  • Thread starter Thread starter Jack
  • Start date Start date
J

Jack

Hi,

I copy and paste from a worksheet to another. It works
well on excel2000 but not on excel97-->runtime
error '1004' delete Method of Range Class Failed. Does it
means the versions are not compatible?.Do you know what is
wrong?!

this is what the button has:

Dim ExecComments As Worksheet
Set ExecComments = Sheets("Comments")

Dim Answers As Worksheet
Set Answers = Sheets("Sheet1")

'delete column
ExecComments.Columns(1).EntireColumn.Delete

request = InputBox("Please enter the question number to
see the comments (1-4)", "", "")

' Find the last row of data
FinalRow = Answers.Range("A65536").End(xlUp).Row


' Loop through each row
For X = 1 To FinalRow

thisValue = Answers.Range("F" & X).Value
If thisValue = "1" Then
thisValueComments = Answers.Range("Z" &
X).Value
If Not thisValueComments = "[NULL]" Then
Answers.Range("Z" & X).Copy

NextRow = NextRow + 2
If flag = 1 Then
ExecComments.Range("A12").Value = "QUESTION
1. what team you have? "
flag = 0
End If

ExecComments.Range("A" & NextRow).Value =
thisValueComments

End If
End If
Next X

Thanks for your help!!!
Jack
 
I may be wrong, but I seem to remember this problem happening to me whe
I used an older version. Try this:

'delete column
ExecComments.Select
Columns(1).EntireColumn.Delete
Sheets("YourOriginalSheetNameHere").Select

I don't have '97 anymore, so cannot test (but the code should work o
all versions). Maybe someone else can shed some more light on it.
 
How are you triggering the code - if with a commandbutton, try changing the
takefocusonclick property to false - just a guess of course.
 
How are you triggering the code - if with a commandbutton,
try changing the takefocusonclick property to false
- just a guess of course.
Click to expand...

Yes, Tom. And a good guess at that! Relatively inexperienced a
applying VB to Excel (97), I've been fighting this problem for too man
hours with MS "Help" offering no clue as to WHY the delete failed wit
every variation I put in the command. Taking the focus off the butto
did the trick, and in retrospect it became clear as to why the delet
failed. Thanks very much!

To
 
Back
Top