Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
ann ann is offline
external usenet poster
 
Posts: 210
Default Where to insert code?

Whoops! That should be:
It still stops in the middle of the file!

Sub FastAcNos()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim AcNo As String
Dim eAc As Long
Dim i As Long
Dim sh As Long
Dim fndAc As Range
Dim tbox As TextBox

On Error GoTo Errorhandler
Application.ScreenUpdating = False
eAc = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("c:\Documents and Settings" & _
"\zzfy98\My Documents\Test") 'change directory

For Each objFile In objFolder.Files
If objFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=objFolder.Path _
& "\" & objFile.Name, UpdateLinks:=False

With Workbooks(objFile.Name)
For sh = 1 To .Sheets.Count
bDone = True
For i = 1 To eAc
If LCase(ThisWorkbook.Sheets("Sheet1") _
.Cells(i, 2).Value) < "yes" Then
' All accounts not found
bDone = False

AcNo = ThisWorkbook.Sheets("Sheet1").Cells(i, 1).Value
With .Sheets(sh).Cells

Set fndAc = .Find(AcNo _
, LookIn:=xlValues _
, Lookat:=xlPart _
, MatchCase:=True)
End With

If Not fndAc Is Nothing Then
ThisWorkbook.Sheets("Sheet1"). _
Cells(i, 2).Value = "Yes"
Else
' Tom's Code:
For Each tbox In .Sheets(sh).TextBoxes
If InStr(1, AcNo, tbox.Text, vbTextCompare) Then
' AcNo found
ThisWorkbook.Sheets("Sheet1").Cells(i, 2).Value = "Yes" ' I
assume???
End If
Next tbox

End If

End If
Next i
If bDone Then
.Close False
Exit Sub
End If
Next sh
.Close False
Set objFile = Nothing
End With
End If
Next

For i = 1 To eAc
With ThisWorkbook.Sheets("sheet1")
If IsEmpty(.Cells(i, 2)) Then
.Cells(i, 2).Value = "No"
End If
End With
Next
Errorhandler:
Application.ScreenUpdating = True
Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
End Sub


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert Code Steph Excel Worksheet Functions 0 February 27th 10 06:08 PM
insert vba code to new workbook Jive Excel Worksheet Functions 2 March 5th 08 02:12 PM
How to insert code Karl Excel Discussion (Misc queries) 3 January 8th 07 11:01 PM
Insert Code Derek Y via OfficeKB.com New Users to Excel 3 October 17th 05 03:48 AM
code to insert add'l code ??? mark kubicki Excel Programming 1 August 16th 04 10:37 PM


All times are GMT +1. The time now is 11:46 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"