| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 *** |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Error when cell A1 is not active and xlInsideVertical border formatthrowing error 1004 | Excel Programming | |||
| Error handling error # 1004 Run-time error | Excel Programming | |||
| Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
| Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
| Automation Error, Unknown Error. Error value - 440 | Excel Programming | |||
![Jim Berglund[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
