Next without For Error

  • Thread starter Thread starter Jim Berglund
  • Start date Start date
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
 
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
 
I also get a 1004 "Method Range of Object_Global Failed" error with the
statement

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

Jim
 
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
 
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.
 
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
 
Thanks. I understand. I thought the Else If's (or Elseif's, which I also
tried)) replaced the need for End If's
Jim
 
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
 
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
 
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
 
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
 
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.
 
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.)
 
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
 
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>.)
 
Back
Top