Next without For Error

J

Jim Berglund

I have been trying to run the following code, and am getting a Next without
For Error
Any Ideas?

Jim Berglund

Sub DelRows()

Dim i, n As Long
Dim wsNumFrum As Worksheet


Set wsNumFrum = Worksheets("Numbers from Reverse Directory")
n = wsNumFrum.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row
For i = 1 To n
If Range(Cells(i, 1)).Value = "" Then
Range(Cells(i, 1), Cells(i, 5)).ClearContents
Else
Range(Cells(i, 1)).Select
If Left(Cells(i, 1), 5).Value = "Lname" Then
Range(Cells(i, 1), Cells(i, 5)).ClearContents
Else
If IsNumeric(Cells(i, 5).Value) Then
Range(Cells(i, 1), Cells(i, 5)).ClearContents
End If
Next
'Sort the data
ActiveWorkbook.Worksheets("Numbers from Reverse
Directory").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Numbers from Reverse
Directory").Sort.SortFields. _
Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Numbers from Reverse Directory").Sort
.SetRange Range("A1:E5001")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


End Sub
 
R

Ron Rosenfeld

I have been trying to run the following code, and am getting a Next without
For Error
Any Ideas?

Jim Berglund

Sub DelRows()

Dim i, n As Long
Dim wsNumFrum As Worksheet


Set wsNumFrum = Worksheets("Numbers from Reverse Directory")
n = wsNumFrum.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row
For i = 1 To n
If Range(Cells(i, 1)).Value = "" Then
Range(Cells(i, 1), Cells(i, 5)).ClearContents
Else
Range(Cells(i, 1)).Select
If Left(Cells(i, 1), 5).Value = "Lname" Then
Range(Cells(i, 1), Cells(i, 5)).ClearContents
Else
If IsNumeric(Cells(i, 5).Value) Then
Range(Cells(i, 1), Cells(i, 5)).ClearContents
End If
Next
'Sort the data
ActiveWorkbook.Worksheets("Numbers from Reverse
Directory").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Numbers from Reverse
Directory").Sort.SortFields. _
Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Numbers from Reverse Directory").Sort
.SetRange Range("A1:E5001")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


End Sub
Click to expand...

You are missing some End If's
--ron
 
J

Jim Berglund

I also get a 1004 "Method Range of Object_Global Failed" error with the
statement

If Range(Cells(i, 1)).Value = "" Then

Jim
 
J

Jim Berglund

Thanks, Ron. That Worked.

How about this line? It's the last one causing problems...
If Left(.Cells(i, 1), 5).Value = "Lname" Then

Jim
 
R

Rick Rothstein

Here is your For..Next loop with the two "End If" statements you omitted
shown in upper case...

For i = 1 To n
If Range(Cells(i, 1)).Value = "" Then
Range(Cells(i, 1), Cells(i, 5)).ClearContents
Else
Range(Cells(i, 1)).Select
If Left(Cells(i, 1), 5).Value = "Lname" Then
Range(Cells(i, 1), Cells(i, 5)).ClearContents
Else
If IsNumeric(Cells(i, 5).Value) Then
Range(Cells(i, 1), Cells(i, 5)).ClearContents
End If
END IF
END IF
Next

Notice that with consistent indenting, it is much easier to see when block
structures (such as If..Then/End If, For..Next, etc.) are not closed
properly.
 
J

JLGWhiz

The real problem is an If without and End If. See if this works.

For i = 1 To n
If Range(Cells(i, 1)).Value = "" Then
Range(Cells(i, 1), Cells(i, 5)).ClearContents
Else
Range(Cells(i, 1)).Select
End If
If Left(Cells(i, 1), 5).Value = "Lname" Then
Range(Cells(i, 1), Cells(i, 5)).ClearContents
ElseIf IsNumeric(Cells(i, 5).Value) Then
Range(Cells(i, 1), Cells(i, 5)).ClearContents
End If
Next
 
J

Jim Berglund

Thanks. I understand. I thought the Else If's (or Elseif's, which I also
tried)) replaced the need for End If's
Jim
 
L

Lars-Åke Aspelin

I have been trying to run the following code, and am getting a Next without
For Error
Any Ideas?

Jim Berglund

Sub DelRows()

Dim i, n As Long
Dim wsNumFrum As Worksheet


Set wsNumFrum = Worksheets("Numbers from Reverse Directory")
n = wsNumFrum.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row
For i = 1 To n
If Range(Cells(i, 1)).Value = "" Then
Range(Cells(i, 1), Cells(i, 5)).ClearContents
Else
Range(Cells(i, 1)).Select
If Left(Cells(i, 1), 5).Value = "Lname" Then
Range(Cells(i, 1), Cells(i, 5)).ClearContents
Else
If IsNumeric(Cells(i, 5).Value) Then
Range(Cells(i, 1), Cells(i, 5)).ClearContents
End If
Next
'Sort the data
ActiveWorkbook.Worksheets("Numbers from Reverse
Directory").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Numbers from Reverse
Directory").Sort.SortFields. _
Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Numbers from Reverse Directory").Sort
.SetRange Range("A1:E5001")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


End Sub
Click to expand...


In you For Next loop how have a number of If statements.
The number of "If" and "End If" does not match. There are 3 "If" but
only one "End If".

Hope this helps / Lars-Åke
 
D

Dave Peterson

This kind of syntax is pretty common:

Range(Cells(i, 1), Cells(i, 5)).ClearContents

But I bet that this syntax isn't doing what you want:
If Range(Cells(i, 1)).Value = "" Then

I bet you meant:
If Cells(i, 1).Value = "" Then

If you use:
If Range(Cells(i, 1)).Value = "" Then

Then cells(i,1) better contain something that looks like an address (or a range
name). Otherwise, you'll get a different error.

Since the default property of that cells(i,1) is value, you're doing:
If Range(Cells(i, 1).value).Value = "" Then

It makes sense if Cells(i,1) contained something like: b99
Then your code would be doing:
if range("B99").value = "" then
 
J

Jim Berglund

OK, Thanks for the explanation. I am down to the short strokes and now have
only one hiccup left (it may have been missed in a previous note)

If Left(.Cells(i, 1), 5).Value = "Lname" Then
.Range(Cells(i, 1), Cells(i, 5)).ClearContents

What's wrong here, please?

Jim
 
R

Ron Rosenfeld

Thanks, Ron. That Worked.

How about this line? It's the last one causing problems...
If Left(.Cells(i, 1), 5).Value = "Lname" Then

Jim
Click to expand...

I don't see that exact line in what you posted to me. If the others responses
don't help, post back with more code, and also define the problem.
--ron
 
P

Phillip Holmes

MCSDPhil

Hi there,
I think that you have the .Value in the wrong place here. Try this and
see if it works.

If Left(.Cells(i, 1).Value, 5) = "Lname" Then .Range(Cells(i, 1),
Cells(i, 5)).ClearContents

Regards, Phil.
 
D

Dave Peterson

If you're using that with/end with structure, then you'll want to qualify all
those range objects (.range() and .cells()).

If Left(.Cells(i, 1), 5).Value = "Lname" Then
.Range(.Cells(i, 1), .Cells(i, 5)).ClearContents

And since I didn't see where you used that with/end with, I'd add it.

Option Explicit
Sub DelRows()

'this declares iRow as a variant and LastRow as long:
'Dim i, n As Long
'you could use:
'Dim i as long, n as long
'but I like separate lines:

'I changed the variable names, too
Dim iRow As Long
Dim LastRow As Long

Dim wsNumFrum As Worksheet

Set wsNumFrum = Worksheets("Numbers from Reverse Directory")

With wsNumFrum
'iRow'd include all the parms for the .find command.
'if you don't, then you'll be getting the parms from the last
'.find -- either by the user or by some other code.
LastRow = .Cells.Find(What:="*", _
After:=.Cells(1), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

For iRow = 1 To LastRow
If .Cells(iRow, 1).Value = "" Then
.Range(.Cells(iRow, 1), .Cells(iRow, 5)).ClearContents
Else
If LCase(Left(.Cells(iRow, 1).Value, 5)) = LCase("Lname") Then
.Range(.Cells(iRow, 1), .Cells(iRow, 5)).ClearContents
Else
If IsNumeric(.Cells(iRow, 5).Value) Then
.Range(.Cells(iRow, 1), .Cells(iRow, 5)).ClearContents
End If
End If
End If
Next iRow

'Since the code is in the previous with statement, it's
'kind of equivalent to:
'with wsNumFrum.sort
With .Sort
.SortFields.Clear
.SortFields.Add _
Key:=Parent.Range("A1"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
.SetRange .Parent.Range("A1:E5001")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
End Sub

======
And I think that this portion:

For iRow = 1 To LastRow
If .Cells(iRow, 1).Value = "" Then
.Range(.Cells(iRow, 1), .Cells(iRow, 5)).ClearContents
Else
If LCase(Left(.Cells(iRow, 1).Value, 5)) = LCase("Lname") Then
.Range(.Cells(iRow, 1), .Cells(iRow, 5)).ClearContents
Else
If IsNumeric(.Cells(iRow, 5).Value) Then
.Range(.Cells(iRow, 1), .Cells(iRow, 5)).ClearContents
End If
End If
End If
Next iRow

can use some if/or logic:

For iRow = 1 To LastRow
If .Cells(iRow, 1).Value = "" _
or LCase(Left(.Cells(iRow, 1).Value, 5)) = LCase("Lname") _
or IsNumeric(.Cells(iRow, 5).Value) Then
.Range(.Cells(iRow, 1), .Cells(iRow, 5)).ClearContents
end if
next irow

(Untested, uncompiled. Watch for typos.)
 
D

Dave Peterson

ps. That code used the syntax from xl2007. If you have to support xl2003 or
earlier, it won't work for those users.

But it's an easy fix to use the old syntax.

With .Range("a1:E1501")
.Cells.Sort _
key1:=.Columns(1), _
order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End With
 
D

Dave Peterson

I changed the variable name from i to iRow when I was almost done.

This line:
'iRow'd include all the parms for the .find command.
used to read:
'I'd include all the parms for the .find command.

(there may be more <vbg>.)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Applying Variables to SORT 4
Undo Macro Action 3
Run sort macro on active worksheet 1
Stay on Active Sheet 2
Clear Check Box 2
VBA 2 Codes 2
vba dynamic 1
Pictures not being sorted in VBA 2

Top