Counter Msgbox

[meakashgfx]

Hi,

I have loop that copy cells from excel files into master file. Instead of showing Msgbox I want to count them how many files are show missing.

here is the code I have:

Private Sub StartCopy_Click()


Dim MyFile As String
Dim erow
Dim Filepath As String
Dim FindString As String
Dim RangeObj As Range


    Filepath = "C:\Excel-Mastermind\1\"
    MyFile = Dir(Filepath)
    Do While Len(MyFile) > 0
        If MyFile = "Master.xlsm" Then Exit Sub
        Workbooks.Open Filepath & MyFile
        Range("B2:D18").Copy
        FindString = Cells(2, "A").Value
        ActiveWorkbook.Close False
        Set RangeObj = Cells.Find(What:=FindString, After:=Cells(1), _
                                  LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
                                  SearchDirection:=xlNext, MatchCase:=False)
        If RangeObj Is Nothing Then
            MsgBox "Not Found", vbExclamation
        Else
            RangeObj.Offset(rowOffset:=3, columnOffset:=0).PasteSpecial
        End If
        MyFile = Dir
    Loop
    MsgBox "Done!"
End Sub

Thanks in advance!

 

[vaskov17]

not tested but:

Private Sub StartCopy_Click()




Dim MyFile As String
Dim erow
Dim Filepath As String
Dim FindString As String
Dim RangeObj As Range
Dim lngCount As Long


    lngCount = 0
    Filepath = "C:\Excel-Mastermind\1\"
    MyFile = Dir(Filepath)
    Do While Len(MyFile) > 0
        If MyFile = "Master.xlsm" Then Exit Sub
        Workbooks.Open Filepath & MyFile
        Range("B2:D18").Copy
        FindString = Cells(2, "A").Value
        ActiveWorkbook.Close False
        Set RangeObj = Cells.Find(What:=FindString, After:=Cells(1), _
                                  LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
                                  SearchDirection:=xlNext, MatchCase:=False)
        If RangeObj Is Nothing Then
            'MsgBox "Not Found", vbExclamation
            lngCount = lngCount + 1
        Else
            RangeObj.Offset(rowOffset:=3, columnOffset:=0).PasteSpecial
        End If
        MyFile = Dir
    Loop
    MsgBox "Done!" & vbCrLf & "Missing files: " & lngCount
End Sub

 

[meakashgfx]

thanks a lot! it's working...