macro doesn't work after downloading office updates?

[Dean]

Below is a simple macro that I got mostly from you fine folks, particularly
Tom O, as I recall. It worked flawlessly. Then, I added a line that has it
skip the i=16 case. It then worked flawlessly too. Yet, today, when I try
to run it in a revised file, it gives me a "compile error: Next without For"
at that very statement I added that skips the i=16 case. I then went back
to a file that I am pretty certain is the first file with that change and it
bombed out the same way. I also notice that though it seems to bomb out at
i=16, it didn't do the i=7 case either. That seems odd although, perhaps,
that is the way compile errors work - they tell you where it will bomb
without actually performing the operations prior to when it will bomb. Can
someone confirm that?

In any event, is there something different in my computer or EXCEL setup
that can be causing this error. I did go to the Microsoft site a few days
ago and added SP2 and SP3 plus all the other recommended things to my Office
XP (EXCEL 2002) setup - could that be it? If so, can someone tell me how to
fix this macro to work?

Thanks!
Dean

Sub DataTablesClearAndSetup()

'recorded 2/1/2007 by Dean
'edited for looping courtesy of TO on 2-2-07

Dim i As Long
Dim rng As Range

Sheets("InputSummary").Select
ActiveSheet.Unprotect

If Len(Trim(Range("N23"))) = 0 Then Exit Sub
Select Case Range("N23")
Case "Promote"
Set rng = Range("E12")
Case "Catch-Up"
Set rng = Range("F12")
Case "1st Hurdle"
Set rng = Range("B11")
Case "Sub-Line"
Set rng = Range("J15")
Case "Structures"
Set rng = Range("B9")
End Select
For i = 7 To 124 Step 9
If i = 16 Then Next i ' this used to be the net IRR table which is now
computed from fee loss
Application.Goto Reference:="R" & i & "C15"
ActiveCell.Range("A1:A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
ActiveCell.Offset(-1, -1).Range("A1:A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Table RowInput:=Range("P2"), ColumnInput:=rng

Next i
Range("N1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
End Sub

 

[Jim Cone]

Dean,

You can't blame Microsoft for this problem.<g>
Only one Next is allowed for each For.
Try this...

For i = 7 To 124 Step 9
If i <> 16 Then
Application.Goto Reference:="R" & i & "C15"
ActiveCell.Range("A1:A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
ActiveCell.Offset(-1, -1).Range("A1:A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Table RowInput:=Range("P2"), ColumnInput:=rng
End If
Next i
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Dean" <[email protected]>
wrote in message
Below is a simple macro that I got mostly from you fine folks, particularly
Tom O, as I recall. It worked flawlessly. Then, I added a line that has it
skip the i=16 case. It then worked flawlessly too. Yet, today, when I try
to run it in a revised file, it gives me a "compile error: Next without For"
at that very statement I added that skips the i=16 case. I then went back
to a file that I am pretty certain is the first file with that change and it
bombed out the same way. I also notice that though it seems to bomb out at
i=16, it didn't do the i=7 case either. That seems odd although, perhaps,
that is the way compile errors work - they tell you where it will bomb
without actually performing the operations prior to when it will bomb. Can
someone confirm that?

In any event, is there something different in my computer or EXCEL setup
that can be causing this error. I did go to the Microsoft site a few days
ago and added SP2 and SP3 plus all the other recommended things to my Office
XP (EXCEL 2002) setup - could that be it? If so, can someone tell me how to
fix this macro to work?

Thanks!
Dean

Sub DataTablesClearAndSetup()
'recorded 2/1/2007 by Dean
'edited for looping courtesy of TO on 2-2-07
Dim i As Long
Dim rng As Range
Sheets("InputSummary").Select
ActiveSheet.Unprotect
If Len(Trim(Range("N23"))) = 0 Then Exit Sub
Select Case Range("N23")
Case "Promote"
Set rng = Range("E12")
Case "Catch-Up"
Set rng = Range("F12")
Case "1st Hurdle"
Set rng = Range("B11")
Case "Sub-Line"
Set rng = Range("J15")
Case "Structures"
Set rng = Range("B9")
End Select
For i = 7 To 124 Step 9
If i = 16 Then Next i ' this used to be the net IRR table which is now
computed from fee loss
Application.Goto Reference:="R" & i & "C15"
ActiveCell.Range("A1:A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
ActiveCell.Offset(-1, -1).Range("A1:A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Table RowInput:=Range("P2"), ColumnInput:=rng
Next i
Range("N1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
End Sub

 

[Guest]

If Statement should always end with "End If". Please replace the for loop
with this code.
For i = 7 To 124 Step 9

 

[Dean]

What you're saying is that the Next statement I had within the If statement
was confusing the For statement right? The weird part is that I know my
macro was working. I must have fixed it myself, at the time, but in a file
that I didn't resave - that's the only thing that makes sense to me.

Thanks to both of you for your simpler solution.

Dean