Run time error 91

  • Thread starter Thread starter richg41
  • Start date Start date
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
 
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
 
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...
 
Back
Top