Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code not working | Excel Programming | |||
Code not working and can't see why | Excel Discussion (Misc queries) | |||
Code is not working....please take a look!!! | Excel Programming | |||
Code not Working - Help please | Excel Programming | |||
Code not working | Excel Programming |