Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel loop error -- 25 rows okay, but 30 too many
I have a function that copies rows from a "template" spreadsheet then
paste the template information on a number of worksheets as determined by a cell value. The problem is the macro works fine for the range (A24:BR52) but doesnt work for (A24:BR55). By doesn't work I mean the following: The location where the macro is run from is separate sheet from any of the pasting destinations and template data (i.e. a third worksheet). Yet when the range hits ~30 lines, the data is pasted into the macro sheet's location --- and into cells beginning at C16 (a really strange location as the others paste beginning in A24) -- as well as the proper destination on the other sheets. Is there a programming error by me or does VB have a glitch/memory problem?? THANK YOU VERY MUCH FOR ANY HELP -- here is the relevant code, with all the code appearing at the very bottom. --------------Relevant code------------------------------------------------- If Target old Then 'The target number of countries, is it increasing? For i = old To Target ' for the increase from current # of countries to target Sheet8.Range("A24:BR90").Copy Sheet1.Cells(i + BeginRow, 1).EntireRow.Hidden = False 'unhide the additional input rows Sheets("Country " & i).Activate ActiveSheet.Visible = True 'unhide the additional input worksheets ActiveSheet.Range("A24:BR90").PasteSpecial ActiveSheet.Range("A1").Select Sheet8.Application.CutCopyMode = False Next i --------------------------------------------------------------(All the code)-------------------- Private Sub Worksheet_Change(ByVal Target As Range) 'Do nothing if more than one cell is changed or content deleted If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Target.Address = "$D$9" Then 'Ensure target is a number before proceeding If IsNumeric(Target) Then 'Stop any possible runtime errors and halting code On Error Resume Next 'Turn off ALL events so macro does not put the code into a loop. Application.EnableEvents = False Application.ScreenUpdating = False ' removes flicker when macro's run Dim old As Integer old = Cells(351, 24) ' reads in the previous number of countries (before the selection for more/less countries) BeginRow = 15 ' -- CHANGE NUMBER - One less than first row of country input rows If Target old Then 'The target number of countries, is it increasing? For i = old To Target ' for the increase from current # of countries to target Sheet8.Range("A24:BR90").Copy 'A24:BR510'Sheet1.Cells(i + BeginRow, 1).EntireRow.Hidden = False 'unhide the additional input rows Sheets("Country " & i).Activate ActiveSheet.Visible = True 'unhide the additional input worksheets ActiveSheet.Range("A24:BR90").PasteSpecial ActiveSheet.Range("A1").Select Sheet8.Application.CutCopyMode = False Next i 'remove copy area on template from memory 'For j = old To Target 'Sheet1.Cells(j + BeginRow, 1).EntireRow.Hidden = False 'unhide the additional input rows 'Next j ElseIf Target < old Then 'The target number of countries, is it decreasing? For i = Target To old ' for the decrease from current # of countries Sheet1.Cells(i + BeginRow + 1, 1).EntireRow.Hidden = True ' Hide the extra input rows 'Sheets("Country " & i).Range("A24:BR510").Clear Sheets("Country " & i + 1).Visible = False ' hide the extra worksheets Next i ' For the one country situation hide mutualized row, allocation columns, ' mutualized worksheet and Summary-all worksheet End If Sheet1.Select If Target = 1 Then Cells(46, 1).EntireRow.Hidden = True Cells(1, 9).EntireColumn.Hidden = True Cells(1, 10).EntireColumn.Hidden = True Cells(1, 23).EntireColumn.Hidden = True Cells(1, 24).EntireColumn.Hidden = True Sheet2.Visible = False Sheet6.Visible = False Sheet3.Rows(12).Hidden = True Sheet3.Rows(13).Hidden = True Sheet3.Rows("53:78").RowHeight = 1.5 Sheet3.Rows("120:162").RowHeight = 1.5 Sheet3.Rows("53:78").EntireRow.Hidden = True Sheet3.Rows("120:162").EntireRow.Hidden = True Else Cells(46, 1).EntireRow.Hidden = False Cells(1, 9).EntireColumn.Hidden = False Cells(1, 10).EntireColumn.Hidden = False Cells(1, 23).EntireColumn.Hidden = False Cells(1, 24).EntireColumn.Hidden = False Sheet2.Visible = True Sheet6.Visible = True Sheet3.Rows(12).Hidden = False Sheet3.Rows(13).Hidden = False Sheet3.Rows("53:78").RowHeight = 12.75 Sheet3.Rows("120:162").RowHeight = 12.75 Sheet3.Rows("53:78").EntireRow.Hidden = False Sheet3.Rows("120:162").EntireRow.Hidden = False End If Cells(351, 24) = Target ' records the number of countries currently selected Application.ScreenUpdating = True ' undoes the stopping of the flicker 'Turn events back on Application.EnableEvents = True 'Allow run time errors again On Error GoTo 0 End If End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel loop error -- 25 rows okay, but 30 too many
Sorry to say, but your code is very hard to read.
I would suggest you rewrite it from scratch, using named ranges, constants and variables instead of all these cell references and row numbers. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA) | Excel Worksheet Functions | |||
Loop thru rows to copy to another excel spreadsheet | Excel Worksheet Functions | |||
How to Delete Rows in Excel In a Do Loop | Excel Worksheet Functions | |||
Excel loop to delete redudent rows | Excel Programming | |||
Excel VBA - Repeat for loop until blank rows | Excel Programming |