satish gubbi
02-19-2013, 07:18 AM
I have below code which would open all the excel files in given path and compare the particular cells and its values with the value given.
BUT it is not working showing error "Run time Error 1004"
Please help me with the code whether it correctly populating the details or not.
Dim fld As Object
Dim fsp As Object
Dim RecordCount As Long
Dim Status As String
Dim Company As String
Dim Prime As String
Dim I As Long
Dim UnmatchedNo As Long
Public Function Calculates()
If Range("A2") = "" Then
s = MsgBox("Please paste the tracker details in column A1", vbExclamation)
Exit Function
End If
Set wbCodeBook = ThisWorkbook
Application.DisplayAlerts = False
Range("B1").Select
Selection.End(xlDown).Select
RecordCount = ActiveCell.Row
Range("B1").Select
Range("T:T").Clear
Range("T1") = "StatusMatching"
UnmatchedNo = 0
Set fsp = CreateObject("Scripting.FileSystemObject")
FolderName = InputBox("Please Input the Folder Path")
Set fld = fsp.getfolder(FolderName)
For Each fil In fld.Files
Workbooks.Open (FolderName & "\" & fil.Name)
Workbooks(fil.Name).Activate
If ActiveWorkbook.Worksheets.Count > 3 Then
Workbooks(fil.Name).Worksheets(1).Select
Status = Range("E22")
Company = Range("B4")
Prime = Range("B5")
For I = 2 To RecordCount
'MsgBox ThisWorkbook.Worksheets("Tracker").Range("B" & I) & "_ " & ThisWorkbook.Worksheets("Tracker").Range("C" & I)
If ThisWorkbook.Worksheets("Tracker").Range("B" & I) = "Company" And ThisWorkbook.Worksheets("Tracker").Range("C" & I) = "Prime" Then
If ThisWorkbook.Worksheets("Tracker").Range("F" & I) = "Status" Then
ThisWorkbook.Worksheets("Tracker").Range("T" & I) = "Yes"
Else
ThisWorkbook.Worksheets("Tracker").Range("T" & I) = "No"
UnmatchedNo = UnmatchedNo + 1
End If
End If
Next I
End If
Workbooks(fil.Name).Close (False)
Next fil
Application.DisplayAlerts = True
Range("A1").Select
If UnmatchedNo > 0 Then
s = MsgBox("Status Matching Completed. ." & Chr(13) & Chr(13) & "No of Unmatched Status : " & UnmatchedNo & Chr(13) & "Please check column T", vbExclamation)
Else
s = MsgBox("Status Matching Completed. ." & Chr(13) & Chr(13) & "All status are Matched!", vbInformation)
End If
End Function
BUT it is not working showing error "Run time Error 1004"
Please help me with the code whether it correctly populating the details or not.
Dim fld As Object
Dim fsp As Object
Dim RecordCount As Long
Dim Status As String
Dim Company As String
Dim Prime As String
Dim I As Long
Dim UnmatchedNo As Long
Public Function Calculates()
If Range("A2") = "" Then
s = MsgBox("Please paste the tracker details in column A1", vbExclamation)
Exit Function
End If
Set wbCodeBook = ThisWorkbook
Application.DisplayAlerts = False
Range("B1").Select
Selection.End(xlDown).Select
RecordCount = ActiveCell.Row
Range("B1").Select
Range("T:T").Clear
Range("T1") = "StatusMatching"
UnmatchedNo = 0
Set fsp = CreateObject("Scripting.FileSystemObject")
FolderName = InputBox("Please Input the Folder Path")
Set fld = fsp.getfolder(FolderName)
For Each fil In fld.Files
Workbooks.Open (FolderName & "\" & fil.Name)
Workbooks(fil.Name).Activate
If ActiveWorkbook.Worksheets.Count > 3 Then
Workbooks(fil.Name).Worksheets(1).Select
Status = Range("E22")
Company = Range("B4")
Prime = Range("B5")
For I = 2 To RecordCount
'MsgBox ThisWorkbook.Worksheets("Tracker").Range("B" & I) & "_ " & ThisWorkbook.Worksheets("Tracker").Range("C" & I)
If ThisWorkbook.Worksheets("Tracker").Range("B" & I) = "Company" And ThisWorkbook.Worksheets("Tracker").Range("C" & I) = "Prime" Then
If ThisWorkbook.Worksheets("Tracker").Range("F" & I) = "Status" Then
ThisWorkbook.Worksheets("Tracker").Range("T" & I) = "Yes"
Else
ThisWorkbook.Worksheets("Tracker").Range("T" & I) = "No"
UnmatchedNo = UnmatchedNo + 1
End If
End If
Next I
End If
Workbooks(fil.Name).Close (False)
Next fil
Application.DisplayAlerts = True
Range("A1").Select
If UnmatchedNo > 0 Then
s = MsgBox("Status Matching Completed. ." & Chr(13) & Chr(13) & "No of Unmatched Status : " & UnmatchedNo & Chr(13) & "Please check column T", vbExclamation)
Else
s = MsgBox("Status Matching Completed. ." & Chr(13) & Chr(13) & "All status are Matched!", vbInformation)
End If
End Function