Use VBA to evaluate a group of words that fit a pattern. (but some do not! doh!)

[Burrgoegee]

I've been searching on Google on how to evaluate a series of words that have a pattern. This is for a home project I have been working on and it's sucking up quite bit of my time.


In column B, I have the following list of words.

ren "apples1_hw.jpg"

ren "apples2_hw.jpg"

ren "apples3_hw.jpg"

ren "chartsa_hw.jpg"

ren "chartsb_hw.jpg"

ren "formula_hw.jpg"

ren "travelagenda_hw.jpg"

etc....


Pattern #1:

As you can see, the first group of words ("apples") is all pretty much the same, the only difference being that there is a "1_hw.jpg"; "2_hw.jpg"; "3_hw.jpg". In similar fashion, the next set of words "charts" is the same except for the ending."a_hw.jpg" & "b_hw.jpg". So it's either going to be a succession in numbers (1 through... 10) or a succession in letters (a through z).


So I was wondering if someone can write a VBA code that can do the following:


ren "apples1_hw.jpg" becomes ==> _layer1.jpg"

ren "apples2_hw.jpg" becomes ==> _layer2.jpg"

ren "apples3_hw.jpg" becomes ==> _layer3.jpg"

ren "chartsa_hw.jpg" becomes ==> _layer1.jpg"

ren "chartsb_hw.jpg" becomes ==> _layer2.jpg"

ren "formula_hw.jpg" becomes ==> _layer.jpg" **

ren "travelagenda_hw.jpg" becomes ==> _layer.jpg" **


** Note

In the last 2 examples, the words formula and travelagenda are completely different from each other. Although they have the same file extension (*.jpg) it is not part of a series of numbers of letters like apples & charts is.


Pattern #2:

ren "5411642_box_650x480.jpg"

ren "8645417_box_451x327.jpg"


The only thing that changes is the numbers that show before & after the underscores. The pattern is the same:

group of 7 digits, underscore (_), the word "box", underscore (_), 3 digits"x" 3digits.jpg


Again, if VBA could be used so that:

ren "5411642_box_650x480.jpg" becomes ==> good.jpg"

ren "8645417_box_451x327.jpg" becomes ==> good.jpg"


Last pattern in my group is similar to the last except much simpler.


Pattern #3:

ren "1234567_1234567.jpg"


It's any group of 7 numbers both before & after the underscore(_). It is not of course always 1234567, - just using it as an example.


ren "1234567_1234567.jpg" becomes ==> very good.jpg"


So if it doesn't fit any of these 3 patterns I've described, i'd like for it to show "DOES NOT FIT.jpg"


My original list of words in sheet 1, col. B. I'd like for the new values to show in the same sheet, column E. I have a header at the top of the sheet so the list actually starts at B2.


Is this possible to do this within VBA? Any help would be greatly appreciated.

 

[NARAYANK991]

Hi ,


I think this should be possible. The only caveat is that everything has to be hard-coded within the VBA procedure ; at a later date , if a new pattern is to be accommodated , your procedure will need to modified.


Narayan