VBA "Like" operator and wildcard

Question

I have a loop which activates a certain file and copies data; it is not working and i cannot see why. Please see below:

For Each w In Workbooks
    If w.Name Like "*File 1*" Then
        Windows(w.Name).Activate
        Sheets("Test").Range("C7:C15").Copy
    End If
Exit For
Next w

The wildcard is there because in reality, each week the name of the file will change from "File 1 - week1" to "File 1 - week2", and so on. The point is, the vode stops at the like operator line, so presuming an issue with the "File 1"?

Any help greatly appreciated

If the name is always "File 1 etc." then `Like` should be `Like "File 1*" ` removing the first wildcard, otherwise it expects something before File 1 — DragonSamu, Aug 16 '16 at 09:47
What is the error message when code stops? (And you should envisage removing all `.Activate` and others `.Select` in your code, they usually are more troublemakers that other thing, see [here](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) ) — Vincent G, Aug 16 '16 at 10:14
@VincentG there is no error message, the code just skips from the If statement to the End if. It does not execute. — mojo3340, Aug 16 '16 at 10:32
The code stops without error message? Weird. Are you sure you didn't put an breakpoint? Or an stop condition on a watch? — Vincent G, Aug 16 '16 at 10:37
@VincentG There are no breaks and no watches. The code does not "stop" per say, it just skips the loop and finishes — mojo3340, Aug 16 '16 at 10:45
@VincentG It has no value, it returns nothing i.e it is blank — mojo3340, Aug 16 '16 at 11:32
Where and what have you set **w** to point to? If it returns nothing then the problem is in the other parts of the code.. Could you add the part where you Dim w? — Han Soalone, Aug 16 '16 at 12:40
Note also that it lists only opened workbooks that run in the same instance of Excel where you run your code.. — Han Soalone, Aug 16 '16 at 12:45
Thank you all for your help. It turns out Ryan is correct; the indentation and position of "Exit For" is so important. The reason the code was skipping the loop was because it genuinely did not find "File 1" after the first try. Repositioning the exit for allowed it to only stop once it found a match. — mojo3340, Aug 16 '16 at 14:32

score 1 · Accepted Answer · answered Aug 16 '16 at 13:25

One issue appears you are exiting after the first iteration. From the way you described the problem, it sounds like you want to stop searching after you've found a match, not after the first try.

Simply move the Exit For, like this:

For Each w In Workbooks
    If w.Name Like "*File 1*" Then
        Windows(w.Name).Activate
        Sheets("Test").Range("C7:C15").Copy
        Exit For
    End If
Next w

VBA "Like" operator and wildcard

1 Answers1