Solved: VBA Code : Help required

[satish gubbi]

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

[Rob342]

1004 error you need to tell it to look at the correct worksheet
which line are you getting the error on ?

[satish gubbi]

Thanks for your reply, I am getting error under below line

Workbooks(fil.Name).Worksheets(1).Select

[Rob342]

Try this

Workbooks(fil.Name).Worksheets(1).Select


to

Dim ws as worksheet
Workbooks(fil.Name).Worksheets(1).Activate
set ws = worksheets(1)
with ws
Status = Range("E22")
Company = Range("B4")
Prime = Range("B5")
Rest of your code

End with
Workbooks(fil.Name).Close (False)

[satish gubbi]

hi Rob,

Thank you very much for your help, its working as intended