excelvbainstr

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


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

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.


Solution

  • 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