VBA if statement msg box help

[slams]

Hello,

I have some code that compares part of a filename to a list in a column then moves the file to a new folder if they match. However I can't seem to get the message boxes right. I want ONE message box to display "Files archived!" when the loop is finished and ALL the files have been moved, but also a msg box to display "File not found" when a file can't be found/doesn't match the list. I can only seem to get a message box to display after every file is moved so..20+ message boxes, or one at the very end regardless if anything has been moved or not


Here's (part) of my code:

For Each Objfile In objFolder.Files

FileNum = Left(Right(Objfile.Path, 13), 8)

Dim RowCount: RowCount = 2
Dim ColIndex: ColIndex = 16

Do

    If Cells(RowCount, ColIndex).Value = FileNum Then
    objFSO.MoveFile Objfile.Path, strFolderPath
    End If
    RowCount = RowCount + 1
    
Loop Until IsEmpty(Cells(RowCount, ColIndex).Value)

Next Objfile

Thanks!

 

[GTO]

WARNING: Very much "air code" and not tested...

In a junk copy of your wb, see if this is close.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> example()<br><SPAN style="color:#00007F">Dim</SPAN> RowCount<br><SPAN style="color:#00007F">Dim</SPAN> ColIndex<br><SPAN style="color:#00007F">Dim</SPAN> sPrompt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> bolFileMoved <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> bolOneOrMoreMoved <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br><br>  <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Objfile <SPAN style="color:#00007F">In</SPAN> objFolder.Files<br>    <br>    bolFileMoved = <SPAN style="color:#00007F">False</SPAN><br>    <br>    FileNum = Left(Right(Objfile.Path, 13), 8)<br>    <br>    RowCount = 2<br>    ColIndex = 16<br>    <br>    <SPAN style="color:#00007F">Do</SPAN><br>    <br>      <SPAN style="color:#00007F">If</SPAN> Cells(RowCount, ColIndex).Value = FileNum <SPAN style="color:#00007F">Then</SPAN><br>        objFSO.MoveFile Objfile.Path, strFolderPath<br>        bolFileMoved = <SPAN style="color:#00007F">True</SPAN><br>        bolOneOrMoreMoved = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Do</SPAN><br>      <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>      <br>      RowCount = RowCount + 1<br>        <br>    <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">Until</SPAN> IsEmpty(Cells(RowCount, ColIndex).Value)<br>    <br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> bolFileMoved <SPAN style="color:#00007F">Then</SPAN><br>      MsgBox "File named: " & FileNum & " was not found/moved", vbInformation, vbNullString<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>  <SPAN style="color:#00007F">Next</SPAN> Objfile<br>  <br>  MsgBox IIf(bolOneOrMoreMoved, "One or more files were archived.", "No files were archived"), vbInformation, vbNullString<br>  <br>  <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Hope that helps,

Mark

 

[slams]

Hi Mark,

I think this is kind of working but I suspect the "File not moved/found" error is coming up every time it finds a file in the folder that doesn't match the list in the column, then moving the matching files and displaying the "One of more filed were archived". Is there anyway to edit it slightly so that "File not moved/found" message comes up only when it can't find a file on the list in the folder?