If Count is over _ amount, and FileName does NOT contain _

Question

I'm looking to construct my IF, Then, Else statement to deal with all possible occurrences in my reports.

• If the number of occurrences of a variable (fCount) is over 6 and the Filename (fName) does NOT contain the string "RADMON", then save the report.
• If the number of occurrences of a variable (fCount) is over 249 and the Filename (fName) DOES contain the string "RADMON", then save the report.
• If neither is true, then Kill the report.

I currently structure it like this:

If fCount > 6 And Not InStr(1, fName, "RADMON", vbTextCompare) = 0 Then 
    ActiveWorkbook.Save 
ElseIf InStr(1, fName, "RADMON", vbTextCompare) = 0 And fCount >= 250 Then 
    ActiveWorkbook.Save
Else
    ActiveWorkbook.Close
    Kill

The result is that is that reports where the fCount is over 6 but fName does not contain "RADMON" are being actively killed by my code.

Answer 1

Your first if statement is a double negative. The InStr function will return the position of the found string. InStr(1, fName, "RADMON", vbTextCompare) = 0 would mean that the string wasn't found, having Not InStr(1, fName, "RADMON", vbTextCompare) = 0 is saying if fCount is greater than 6 and "RADMON" was found, save the file.

If fCount > 6 And InStr(1, fName, "RADMON", vbTextCompare) = 0 Then 
  ActiveWorkbook.Save 
ElseIf InStr(1, fName, "RADMON", vbTextCompare) > 0 And fCount >= 250 Then 
  ActiveWorkbook.Save
Else
  ActiveWorkbook.Close
  Kill
End if