Run time error 91

R

richg41

I am such a novice at this....I have the following code that partiall
works...(guess that means it doesn't work!)

I can cycle through the first do while loop and then the code crashe
with a run time error 91 -- Object variable or With Block variable no
set

Any support is much appreciated...I have very little pride o
authorship, I am just hoping to have code that does what I need.

What I simply need to do is look into a directory and read the files.
Then I want to cycle through the files looking into column "i" and fin
ALL instances of the word 'closed" Then I want to remove that row an
place it on a worksheet in the same workbook named closed. I want t
be able to repeat this for each file in the directory.


Here is what I have:



Private Sub CommandButton6_Click()
Application.ScreenUpdating = True 'to see whats happening
Dim i As Integer
With Application.FileSearch 'find files
.NewSearch
.LookIn = "C:\Test" 'Amend to suit
.SearchSubFolders = False
.Filename = "*.xls"
.Execute
For i = 1 To .FoundFiles.Count 'increment count
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))
'set first found file??
With wb.Worksheets("OPEN") 'name of worksheet is OPEN
' moves closed items in column i to sheet named closed
.Columns("I:I").Select ' CAUSES APPLICATION DEFINED ERROR
Do While Selection.Find(what:="closed", After:=ActiveCell
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate



I can step through the code to this point then the error messag
appears.



ActiveCell.EntireRow.Select
Selection.Copy
ActiveSheet.Next.Select
Application.Goto Reference:="R3C1"
Selection.Insert shift:=xlDown
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=0
ActiveSheet.Previous.Select
Selection.ClearContents
Selection.ClearContents
Selection.Delete shift:=xlUp
.Columns("I:I").Select
Loop
wb.Save
wb.Close
End With
Next i
End With
Application.ScreenUpdating = True
End Sub-I can step through the code to this point
 
E

Earl Kiosterud

Rich,

I tinkered a bit, and didn't get your error. I didn't loop through your
workbooks yet, but it found the first row with "closed" in column I. See if
this gets you closer to your objective.

Private Sub CommandButton6_Click()
Application.ScreenUpdating = True 'to see whats happening
Dim i As Integer
Dim wb As Workbook
With Application.FileSearch 'find files
.NewSearch
.LookIn = "C:\Test" 'Amend to suit
.SearchSubFolders = False
.Filename = "*.xls"
.Execute

For i = 1 To .FoundFiles.Count 'increment count
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))
'set first found file??

With wb.Worksheets("OPEN") 'name of worksheet is OPEN
' moves closed items in column i to sheet named closed
.Columns("I:I").Select ' CAUSES APPLICATION DEFINED ERROR
Do While Selection.Find(what:="closed", After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.EntireRow.Cut
' you can now paste the row into your list

Loop
End With
' code to close the workbook
Next i
End With
End Sub
Also, since you want all the files in the directory, Dir() is an easy way to
do it instead of
 
D

Dave Peterson

You've got one more reply at your original post.

richg41 < said:
I am such a novice at this....I have the following code that partially
works...(guess that means it doesn't work!)

I can cycle through the first do while loop and then the code crashes
with a run time error 91 -- Object variable or With Block variable not
set

Any support is much appreciated...I have very little pride of
authorship, I am just hoping to have code that does what I need.

What I simply need to do is look into a directory and read the files.
Then I want to cycle through the files looking into column "i" and find
ALL instances of the word 'closed" Then I want to remove that row and
place it on a worksheet in the same workbook named closed. I want to
be able to repeat this for each file in the directory.

Here is what I have:

Private Sub CommandButton6_Click()
Application.ScreenUpdating = True 'to see whats happening
Dim i As Integer
With Application.FileSearch 'find files
NewSearch
LookIn = "C:\Test" 'Amend to suit
SearchSubFolders = False
Filename = "*.xls"
Execute
For i = 1 To .FoundFiles.Count 'increment count
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))
'set first found file??
With wb.Worksheets("OPEN") 'name of worksheet is OPEN
' moves closed items in column i to sheet named closed
Columns("I:I").Select ' CAUSES APPLICATION DEFINED ERROR
Do While Selection.Find(what:="closed", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

I can step through the code to this point then the error message
appears.

ActiveCell.EntireRow.Select
Selection.Copy
ActiveSheet.Next.Select
Application.Goto Reference:="R3C1"
Selection.Insert shift:=xlDown
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=0
ActiveSheet.Previous.Select
Selection.ClearContents
Selection.ClearContents
Selection.Delete shift:=xlUp
Columns("I:I").Select
Loop
wb.Save
wb.Close
End With
Next i
End With
Application.ScreenUpdating = True
End Sub-I can step through the code to this point-
Click to expand...
 
Top