Macro to Skip to the next file IF match a string in a range

[dellzy]

Hye Excel Experts,

I have this part of the macros that actually merge multiple workbooks in a folder. There are certain items need to be filtered in the raw file first before it copies the final one into the master workbook. However, the raw file contributors sometimes park the wrong file content into that folder. However, it is still processed by my macros because it doesn't return error. So, I wish to have the first line of command that actually check through sets of headers in a range of cells and if any of the header names or part of the header names match to a particular string, the macro should directly close the file without save and go to the next file.

Here are parts of what I tried but doesn't work :-

Do While FileName <> ""
Application.ScreenUpdating = False
Workbooks.Open FileName:=myPath & FileName, ReadOnly:=True, local:=True
For Each Sheet In ActiveWorkbook.Sheets
If Range("A1:F1").Value = "Note" Then Resume Next     '<<== the condition that I tried but failed

Appreciate if someone with solution could share for mine.

Thanks in advance.
DZ

 

[JonXL]

You can use the countif worksheet function,

Dim lnBadFile As Long

With ws
    lnBadFile = WorksheetFunction.Countif(.Range("1:1"), "*badheader*")
End With 

If lnBadFile > 0 Then
     'wrong file
Else
     'Right file
End If

Replace ws with your worksheet and badheader with the text you don't want in the file when you process it.

 

[dellzy]

You can use the countif worksheet function,

Dim lnBadFile As Long

With ws
    lnBadFile = WorksheetFunction.Countif(.Range("1:1"), "*badheader*")
End With 

If lnBadFile > 0 Then
     'wrong file
Else
     'Right file
End If

Replace ws with your worksheet and badheader with the text you don't want in the file when you process it.

Hi JonXL,

Thanks for your swift response. But I still need to know what is the right command line IF the wrong file is found? What is the right command to let the file being closed and skip from further macros?

Thanks.

 

[JonXL]

Workbooks("BadFile.xls").Close SaveChanges:=False

 

[dellzy]

Workbooks("BadFile.xls").Close SaveChanges:=False

Hye JonXL,

Thanks again for your response. I've tried this. Unfortunately, since I have this rule on Do While Filename <>"", it went looping non-stop until I had to forcestop it with Ctrl+Esc. I tried to add another few lines of command to allow those "bad" files to be closed without save then move to another folder named "Bad". Logically i would say it should work perfectly.. it worked for a few files, but, i don't know why, the coding went crazy running and the final "bad" file doesn't moves to the "Bad" folder nor the good file been processed.

Is there any condition i need to remove/add/modify?

Thanks a lot.

 

[JonXL]

I think you're going to have to post your code...