Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 5th, 2007, 09:53 AM
Registered User
 
Join Date: Jun 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Excel Macro works in 2000 but not 2003

I have a problem that occurs with MS Excel 2003 Professional but not MS Excel 2000 Professional. I am on Windows XP.

The Macro runs without an error message, but instead of completing, it seems to be stuck in a loop where it keeps forcing blank lines to print.
The piece of code causing the problem is below:

Set myRange = ActiveSheet.UsedRange
For Each rw In myRange.Rows

If insertNextRow Then
Rows(rw.Row).Select
Selection.Insert Shift:=xlDown
End If

insertNextRow = False
If rw.OutlineLevel = 3 Then
insertNextRow = True
End If

Next rw

This part of the macro inserts a row between subtotalled groups only. This works in Excel 2000 but in Excel 2003 it will keep inserting lines forever unless you break out of the macro.



 
Old June 5th, 2007, 01:40 PM
Friend of Wrox
 
Join Date: Feb 2007
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Hmm... It's probably adding the current line to the 'used range' and therefore the next line will always have that indent level, therefore always be true. If that's the case, this should fix it and should work for both:
--------------------------------------------------------------------
Private Sub CommandButton1_Click()

Set myRange = ActiveSheet.UsedRange

For Each rw In myRange.Rows

If insertNextRow Then
  Rows(rw.Row).Select
  Selection.Insert Shift:=xlDown
End If

If rw.OutlineLevel = 3 And Not insertNextRow _
  Then insertNextRow = True _
  Else insertNextRow = False

Next

End Sub
--------------------------------------------------------------------
Hope this works for you






Similar Threads
Thread Thread Starter Forum Replies Last Post
execute xl macro in DTS 2000 activex script task ismailc SQL Server DTS 1 December 14th, 2007 10:14 AM
Excel 2000 vs Excel 2002 Macro Issue williadn Excel VBA 1 July 14th, 2005 09:09 AM
APPL_PHYSICAL_PATH (2000 vs 2003) asinning ASP.NET 1.0 and 1.1 Professional 0 March 10th, 2005 10:47 AM
Excel in win 2000 to excel winxp (excel 2002) Max Excel VBA 3 August 28th, 2003 04:44 AM
Outlook 2000 - Macro trouble brunette VB How-To 0 August 22nd, 2003 06:40 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.