find_audit

Function Sh_Exist(wb As Workbook, sName As String) As Boolean
    Dim wsSh As Worksheet
    On Error Resume Next
    Set wsSh = wb.Sheets(sName)
    Sh_Exist = Not wsSh Is Nothing
End Function

Sub check()
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    sFilePath = "path"
    checkpath = "path"


    'Sh_Exist function checks existence of sheet to save datas
    'find_ogrn is finding on Sheet1 everything. If does not find => N/A OGRN
    'find_name is the same as find_ogrn, bit N/A Auditor
    'It's necessary to open book from we should take vars
    'sFileName the files where we try to find our vars
    '


    Dim wbCheck As Workbook
    Count = 0

    'Check for back slash

    If Right(sFilePath, 1) <> "\" Then
        sFilePath = sFilePath & "\"
    End If
    If Right(checkpath, 1) <> "\" Then
       checkpath = checkpath & "\"
    End If


    checkname = Dir(checkpath & "where_find.xlsm")

    'Workbooks.Open Filename:="path" & checkname
    sFileName = Dir(sFilePath & "*.xlsx")

    Set wbCheck = Workbooks(checkname)

    If Not Sh_Exist(wbCheck, "output2") Then
        wbCheck.Sheets.Add(, wbCheck.Sheets(wbCheck.Sheets.Count)).Name = "output2"
    End If
    Sheet_work = "output2"
    Workbooks(checkname).Sheets(Sheet_work).Cells(1, 2).Value = "smth"
    Workbooks(checkname).Sheets(Sheet_work).Cells(1, 1).Value = "File_name"
    Workbooks(checkname).Sheets(Sheet_work).Cells(1, 3).Value = "smth"
    j = 1
    k = 1
    Do While Len(sFileName) > 0
        If Right(sFileName, 4) = "xlsx" Then
            'Display file name in immediate window
            'Debug.Print sFileName
            Workbooks.Open Filename:=sFilePath & sFileName
                    For i = 2 To 4974 'last row of file
                        find_ogrn = Workbooks(checkname).Sheets("Sheet1").Cells(i, 4).Value
                        find_name = Workbooks(checkname).Sheets("Sheet1").Cells(i, 2).Value
                        With Workbooks(sFileName).Sheets("Sheet1").Range("A1:A500")
                            'lastrow forfile with names
                            'Set mycell = .Find(what:=Workbooks(checkname).Worksheet("Sheet1").Cells(i, 2).Value, LookAt:=xlWhole)
                            Set x = .Find(find_ogrn)
                            Set y = .Find(find_name)
                            If Not x Is Nothing Then
                                j = j + 1
                                Count = Count + 1
                                Debug.Print (sFileName & " " & x.Row & " " & find_ogrn & " " & "iter: " & k)
                                Workbooks(checkname).Sheets(Sheet_work).Cells(j, 3).Value = Workbooks(checkname).Sheets("Sheet1").Cells(i, 4).Value
                                Workbooks(checkname).Sheets(Sheet_work).Cells(j, 2).Value = "smth"
                                Workbooks(checkname).Sheets(Sheet_work).Cells(j, 1).Value = sFileName
                            End If
                            If Not y Is Nothing Then
                                j = j + 1
                                Count = Count + 1
                                Debug.Print (sFileName & " " & y.Row & " " & find_name)
                                Workbooks(checkname).Sheets(Sheet_work).Cells(j, 3).Value = Workbooks(checkname).Sheets("Sheet1").Cells(i, 2).Value
                                Workbooks(checkname).Sheets(Sheet_work).Cells(j, 2).Value = "smth"
                                Workbooks(checkname).Sheets(Sheet_work).Cells(j, 1).Value = sFileName
                            End If
                        End With
                    Next i
            'Workbooks(sFileName).Save
            If Count = 1 Then
                If IsNumeric(Workbooks(checkname).Sheets(Sheet_work).Cells(j, 3).Value) = True Then
                    j = j + 1

                    Workbooks(checkname).Sheets(Sheet_work).Cells(j, 3).Value = "N/A smth"
                    Workbooks(checkname).Sheets(Sheet_work).Cells(j, 2).Value = "smth"
                    Workbooks(checkname).Sheets(Sheet_work).Cells(j, 1).Value = sFileName
                Else:
                    j = j + 1

                    Workbooks(checkname).Sheets(Sheet_work).Cells(j, 3).Value = "N/A smth"
                    Workbooks(checkname).Sheets(Sheet_work).Cells(j, 2).Value = "smth"
                    Workbooks(checkname).Sheets(Sheet_work).Cells(j, 1).Value = sFileName
                End If

            ElseIf Count = 0 Then
                j = j + 1

                Workbooks(checkname).Sheets(Sheet_work).Cells(j, 3).Value = "N/A smth"
                Workbooks(checkname).Sheets(Sheet_work).Cells(j, 2).Value = "smth"
                Workbooks(checkname).Sheets(Sheet_work).Cells(j, 1).Value = sFileName

                j = j + 1

                Workbooks(checkname).Sheets(Sheet_work).Cells(j, 3).Value = "N/A smth"
                Workbooks(checkname).Sheets(Sheet_work).Cells(j, 2).Value = "smth"
                Workbooks(checkname).Sheets(Sheet_work).Cells(j, 1).Value = sFileName

            ElseIf Count >= 2 Then
                If Workbooks(checkname).Sheets(Sheet_work).Cells(j, 3).Value = Workbooks(checkname).Sheets(Sheet_work).Cells(j - 1, 3).Value And IsNumeric(Workbooks(checkname).Sheets(Sheet_work).Cells(j, 3).Value) = True Then

                    Workbooks(checkname).Sheets(Sheet_work).Cells(j, 3).Value = "N/A smth"
                    Workbooks(checkname).Sheets(Sheet_work).Cells(j, 2).Value = "smth"
                    Workbooks(checkname).Sheets(Sheet_work).Cells(j, 1).Value = sFileName

                ElseIf Workbooks(checkname).Sheets(Sheet_work).Cells(j, 3).Value = Workbooks(checkname).Sheets(Sheet_work).Cells(j - 1, 3).Value And IsNumeric(Workbooks(checkname).Sheets(Sheet_work).Cells(j, 3).Value) = False Then

                    Workbooks(checkname).Sheets(Sheet_work).Cells(j, 3).Value = "N/A smth"
                    Workbooks(checkname).Sheets(Sheet_work).Cells(j, 2).Value = "smth"
                    Workbooks(checkname).Sheets(Sheet_work).Cells(j, 1).Value = sFileName
                End If
            End If
            Workbooks(sFileName).Close
            k = k + 1
        End If
        'Set the fileName to the next available file
        sFileName = Dir
        Count = 0
        'checkname = Dir
    Loop

    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub