Code not working NOW ? But it was before???
 

Dim basebook As Workbook
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim input1 As String
Dim input2 As String
input1 = Application.InputBox("Enter the Quote Number to Find", "Splice
Tech Pty Ltd Unanderra NSW..")
SaveDriveDir = CurDir
MyPath = "Z:\Costing Sheets"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
Do While FNames < ""
Set mybook = Workbooks.Open(FNames)
On Error Resume Next
Dim i As Integer

mybook.Activate
For i = 2 To Sheets.Count
Application.DisplayAlerts = False
If mybook.Worksheets(i).Range("J59").Value = input1 Then
mybook.Worksheets(i).Copy
After:=basebook.Sheets(basebook.Sheets.Count)
ActiveSheet.Name = mybook.Name & " " & "Sheet" & " " &
ActiveSheet.Name
On Error GoTo 0
End If
Next
mybook.Close False
FNames = Dir()

ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True

Loop

The above code searches the designated folder through ALL .xls(Excel files)
and looks for a matching value in cell(J59) from what was placed into the
input box.
Just recently it stopped working.
It seems to search throught he designated folder, but fails to find anything
to match, ALTHOUGH there is a match.

Any ideas how to rectify this?

Corey....

Code not working NOW ? But it was before???
 

try using

If strComp(mybook.Worksheets(i).Range("J59").Value, input1,vbTextCompare) =
0 Then

or

If lcase(mybook.Worksheets(i).Range("J59").Value) = lcase(input1) Then

--
regards,
Tom Ogilvy


"Corey" wrote in message
...
Dim basebook As Workbook
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim input1 As String
Dim input2 As String
input1 = Application.InputBox("Enter the Quote Number to Find",
"Splice Tech Pty Ltd Unanderra NSW..")
SaveDriveDir = CurDir
MyPath = "Z:\Costing Sheets"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
Do While FNames < ""
Set mybook = Workbooks.Open(FNames)
On Error Resume Next
Dim i As Integer

mybook.Activate
For i = 2 To Sheets.Count
Application.DisplayAlerts = False
If mybook.Worksheets(i).Range("J59").Value = input1 Then
mybook.Worksheets(i).Copy
After:=basebook.Sheets(basebook.Sheets.Count)
ActiveSheet.Name = mybook.Name & " " & "Sheet" & " " &
ActiveSheet.Name
On Error GoTo 0
End If
Next
mybook.Close False
FNames = Dir()

ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True

Loop

The above code searches the designated folder through ALL .xls(Excel
files) and looks for a matching value in cell(J59) from what was placed
into the input box.
Just recently it stopped working.
It seems to search throught he designated folder, but fails to find
anything to match, ALTHOUGH there is a match.

Any ideas how to rectify this?

Corey....

Code not working NOW ? But it was before???
 

Thanks Tom

--
Regards

Corey


"Tom Ogilvy" wrote in message
...

try using

If strComp(mybook.Worksheets(i).Range("J59").Value, input1,vbTextCompare)
= 0 Then

or

If lcase(mybook.Worksheets(i).Range("J59").Value) = lcase(input1) Then

--
regards,
Tom Ogilvy


"Corey" wrote in message
...
Dim basebook As Workbook
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim input1 As String
Dim input2 As String
input1 = Application.InputBox("Enter the Quote Number to Find",
"Splice Tech Pty Ltd Unanderra NSW..")
SaveDriveDir = CurDir
MyPath = "Z:\Costing Sheets"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
Do While FNames < ""
Set mybook = Workbooks.Open(FNames)
On Error Resume Next
Dim i As Integer

mybook.Activate
For i = 2 To Sheets.Count
Application.DisplayAlerts = False
If mybook.Worksheets(i).Range("J59").Value = input1 Then
mybook.Worksheets(i).Copy
After:=basebook.Sheets(basebook.Sheets.Count)
ActiveSheet.Name = mybook.Name & " " & "Sheet" & " " &
ActiveSheet.Name
On Error GoTo 0
End If
Next
mybook.Close False
FNames = Dir()

ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True

Loop

The above code searches the designated folder through ALL .xls(Excel
files) and looks for a matching value in cell(J59) from what was placed
into the input box.
Just recently it stopped working.
It seems to search throught he designated folder, but fails to find
anything to match, ALTHOUGH there is a match.

Any ideas how to rectify this?

Corey....