ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Next without For Error (https://www.excelbanter.com/excel-programming/442827-next-without-error.html)

Jim Berglund[_2_]
Next without For Error
 
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

Ron Rosenfeld
Next without For Error
 
On Wed, 26 May 2010 11:00:40 -0600, "Jim Berglund" wrote:

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


You are missing some End If's
--ron

Jim Berglund[_2_]
Next without For Error
 
I also get a 1004 "Method Range of Object_Global Failed" error with the
statement

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

Jim
"Jim Berglund" wrote in message
...
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

Jim Berglund[_2_]
Next without For Error
 
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

"Ron Rosenfeld" wrote in message
...
On Wed, 26 May 2010 11:00:40 -0600, "Jim Berglund"
wrote:

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


You are missing some End If's
--ron


Rick Rothstein
Next without For Error
 
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.

--
Rick (MVP - Excel)



"Jim Berglund" wrote in message
...
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

JLGWhiz[_2_]
Next without For Error
 
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




"Jim Berglund" wrote in message
...
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

Jim Berglund[_2_]
Next without For Error
 

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

"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)



"Jim Berglund" wrote in message
...
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

Lars-Åke Aspelin[_4_]
Next without For Error
 
On Wed, 26 May 2010 11:00:40 -0600, "Jim Berglund"
wrote:

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



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

Dave Peterson
Next without For Error
 
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





Jim Berglund wrote:

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


--

Dave Peterson

Jim Berglund[_2_]
Next without For Error
 
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



"Dave Peterson" wrote in message
...
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





Jim Berglund wrote:

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


--

Dave Peterson


Ron Rosenfeld
Next without For Error
 
On Wed, 26 May 2010 11:24:06 -0600, "Jim Berglund" wrote:

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

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

Phillip Holmes
Next without For Error
 


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.


*** Sent via Developersdex http://www.developersdex.com ***

Dave Peterson
Next without For Error
 
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.)



Jim Berglund wrote:

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

"Dave Peterson" wrote in message
...
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





Jim Berglund wrote:

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


--

Dave Peterson

--

Dave Peterson

Dave Peterson
Next without For Error
 
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



Jim Berglund wrote:

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

"Dave Peterson" wrote in message
...
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





Jim Berglund wrote:

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


--

Dave Peterson

--

Dave Peterson

Dave Peterson
Next without For Error
 
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.)

Dave Peterson wrote:

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.)

Jim Berglund wrote:

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

"Dave Peterson" wrote in message
...
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





Jim Berglund wrote:

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


--

Dave Peterson

--

Dave Peterson

--

Dave Peterson


All times are GMT +1. The time now is 04:36 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com