.insert difference between 97 and 00

[Sean T.]

Hello Group,

I'm inserting rows via vba with the line:

RangeRow.EntireRow.Resize(intNumRows).Insert
shift:=xlShiftDown

This method works perfectly with Excel 2000 but generates
an error in 97. Any ideas?

Sean

 

[Guest]

Sean,

How about trying:
RangeRow.EntireRow.Resize(intNumRows).Insert shift:=xlDown

xlShiftDown isn't a constant in Excel.

Hope it works.

E. Froma

 

[Norman Jones]

Hi E Foma,

From the intermediate wuindow:

?xlShiftDown
-4121
?xlDown
-4121

 

[Guest]

Norman,

You're right. Both are constants in Excel 2000. Maybe not in '97?

E. Froma

 

[Norman Jones]

Hi E Froma,

xlShiftDown is valid in xl97.

I do not have a loaded copy of xl97 to hand, but I am sure that a Google
groups search limited to pre-2000 posts would confirm.

 

[Norman Jones]

Hi Sean,

What error are you getting and how have you set RangeRow?

I assume that the your code is a single line which has wrapped in the post.

 

[Tom Ogilvy]

set RangeRow = Range("B9")
intNumRows = 5
RangeRow.EntireRow.Resize(intNumRows).Insert shift:=xlShiftDown


worked fine for me in Excel 97. You must have made some other error or the
sheet is protected.

 

[Guest]

Thanks to all responders,

And, I apologize for getting back so late (Work continues on the weekend).

Tom, I verified that the sheet was not protected and still am unable to run
the code below. There is no problem inserting the rows manually but, without
the error check, the macro comes to a hault at the resize line.

Option Explicit

Sub NewTask()
Dim RangeCounter As Range
Dim RangeRow As Range
Dim strIndex As String
Dim intNumRows As Integer
Application.ScreenUpdating = False
For Each RangeCounter In Range("A:A")
If RangeCounter.Value = "Index" Then
intNumRows = 4 'Temp assignment
strIndex = RangeCounter.Row
Set RangeRow = Range("A" & strIndex)
On Error GoTo ErrBailOut
RangeRow.EntireRow.Resize(intNumRows).Insert shift:=xlShiftDown
Exit For
End If
Next
ErrBailOut:
Set RangeRow = Nothing
Application.ScreenUpdating = True
End Sub

Sean

 

[Guest]

O'boy,

After further testing the problem has become more convoluted. I closed and
reopened the file and was able to run the code posted above from the VBAIDE.
When I attempted to run the code via a "Command Button", the problem line
generated an error. Not only that but the line now generated an error back
in the IDE.

I repeated the Close/Open but could not get it to run in the IDE. I
substituted in Tom's code and got it to run once before it too started to
fail with the inclusion of a "Command Button".

Perhaps it would be easier to upgrade the old laptop with a more modern
Excel install.

I do appreciate the assistance,
Sean

 

[Dave Peterson]

There's a bug in xl97 that can be resolved by changing the .takefocusonclick
property of the commandbutton to false.

Or you can add:

activecell.activate

at the top of the code.

It was fixed in xl2k.

 

[Guest]

Once again, thanks for all of the replies,

Mr. Peterson did hit upon the cause of my problem and his
recommendation has my worksheet up and running again.
Perhaps I'll keep Excel97 around just a little bit longer.

Sean

 

[Dave Peterson]

Sometimes when you include the name of the procedure or how it's called, you'll
get a correct answer more quickly.

That's the reason I could post the correct response. (You previous message had
that info in it.)


(Glad you got it working.)