VBA Find & Replace (evaluate words in a series)

[Burrgogi]

In col. B, I have the following:
apples1_hw.jpg
apples2_hw.jpg
chartsa_hw.jpg
chartsb_hw.jpg
chartsc_hw.jpg
travelagenda_hw.jpg
cooler_x1.jpg
toys_x1.jpg
38425816_7928501.jpg

In col. E, I want the following result:
_layer1.jpg
_layer2.jpg
_layer1.jpg
_layer2.jpg
_layer3.jpg
_layer.jpg
_cover.jpg
_cover.jpg
_cover.jpg

Basically everything will end with _layer.jpg. There are some variations of course. Apples1_hw.jpg; Apples2_hw.jpg etc...
So in Col E, it should say _layer1.jpg; _layer2.jpg. Same thing with the "charts" example. There is a 'a' 'b' & 'c' at the end of the word charts. So in col. E, it should say _layer1.jpg; _layer2.jpg; _layer3.jpg.

I recorded a very simple macro:
Find: *1_hw.txt
Replace with: _layer1.jpg

Find: *2_hw.jpg
Replace with: _layer2.jpg

Find: *3_hw.jpg
Replace with: _layer3.jpg

Find: *a_hw.jpg
Replace with: _layer1.jpg

Find: *b_hw.jpg
Replace with: _layer2.jpg

Find: *c_hw.jpg
Replace with: _layer3.jpg

Here's the problem. Not everthing will be a series such as "chartsa, chartsb, chartsc...". If you look at the example above
"travelagenda_hw.jpg" The word "agenda" just so happens to end in the letter 'a'. So my find & replace will not work because it's going to give me: _layer1.jpg which is not the desired result.

2nd part of this problem:
The very last item the list (col A) is:
3425816_7928501.jpg

I don't know of any suitable "find & replace" that will give me:
_cover.jpg

Is there anyone that would be willing to write a VBA routine that will solve these problems for me??

 

[taurean]

Untested but it should get you started:

Public Sub SetLayers()
Dim lLR as Long
lLR = Range("B" & Rows.Count).End(xlUp).Row
If lLR < 2 Then Exit Sub
For i = 2 to lLR
    Select Case Right(Range("B" & i).Value2, 8)
    Case "1_hw.jpg"
    Range("E" & i).Value2 = "_layer1.jpg"
    Case "2_hw.jpg"
    Range("E" & i).Value2 = "_layer2.jpg"
    Case "3_hw.jpg"
    Range("E" & i).Value2 = "_layer3.jpg"
    Case "a_hw.jpg"
    Range("E" & i).Value2 = "_layer1.jpg"
    Case "b_hw.jpg"
    Range("E" & i).Value2 = "_layer2.jpg"
    Case "c_hw.jpg"
    Range("E" & i).Value2 = "_layer3.jpg"
    Case Else
    Range("E" & i).Value2 = "_cover.jpg"
Next i
End Sub

 

[Peter_SSs]

Is there a list of items that could have an 'a', 'b' etc appended requiring a '_layer1.jpg'. Otherwise, how can we tell that 'chartsa' should get a '1' but 'travelagenda' does not?

Also, how would we know what gets 'cover' instead of 'layer'? Again, is there a list or is it everything not ending with '_hw.jpg'?

 

[Burrgogi]

I realize it's been a while since I posted my question. I had some personal things come up so I had to put this on the back burner since then. I have an opportunity to run the VBA routine posted by taurean, and I'm getting an error message "Compilation Error: Next without For" at the following line:

Next i

Is there a list of items that could have an 'a', 'b' etc appended requiring a '_layer1.jpg'. Otherwise, how can we tell that 'chartsa' should get a '1' but 'travelagenda' does not?

Peter_SS to answer your question, there aren't too many words in my spread sheet that ends with 'a'. However, let's suppose for example I have the following:
(cell A1) travelagendaa_hw.jpg
(cell A2) travelagendab_hw.jpg
(cell A3) travelagendac_hw.jpg

Essentially I guess what I'm looking for is a VBA routine that can determine if it's a series or not. In another words, all 3 cell contents are the same except for the fact that there is an, a, b, & c that follow the word "travelagenda". It doesn't even have to be the word "travelagenda" - but I'm using it to answer your question.

Further down in the column, let's suppose for example I have:
(cell A10) formula_hw.jpg
(cell A11) pandora_hw.jpg

In this case, it's not part of a series. Although the two words end with an 'a' - the two words formula & pandora are completely different.

how would we know what gets 'cover' instead of 'layer'? Again, is there a list or is it everything not ending with '_hw.jpg'?

There are only two condition where it should say 'cover'. If there are a minimum of 6 numbers before & after the _ (underscore).

3425816_7928501.jpg

Second condition is whenever the cell content has:
{random series of 7 digits}_front_###x###.jpg

### could be any 3 digits.

 

[taurean]

Sorry. Select statement needs to End. See the red marked line of code.

Public Sub SetLayers()
Dim lLR as Long
lLR = Range("B" & Rows.Count).End(xlUp).Row
If lLR < 2 Then Exit Sub
For i = 2 to lLR
    Select Case Right(Range("B" & i).Value2, 8)
    Case "1_hw.jpg"
    Range("E" & i).Value2 = "_layer1.jpg"
    Case "2_hw.jpg"
    Range("E" & i).Value2 = "_layer2.jpg"
    Case "3_hw.jpg"
    Range("E" & i).Value2 = "_layer3.jpg"
    Case "a_hw.jpg"
    Range("E" & i).Value2 = "_layer1.jpg"
    Case "b_hw.jpg"
    Range("E" & i).Value2 = "_layer2.jpg"
    Case "c_hw.jpg"
    Range("E" & i).Value2 = "_layer3.jpg"
    Case Else
    Range("E" & i).Value2 = "_cover.jpg"
[COLOR=red]    End Select[/COLOR]
Next i
End Sub

 

[Burrgogi]

Sorry. Select statement needs to End. See the red marked line of code.[/CODE]

OK taurean, I've made the correction to the code you posted and it's not really working well. In column E, I get the following:

_cover.jpg
_cover.jpg
_cover.jpg
_cover.jpg
_cover.jpg
_cover.jpg
_cover.jpg
_cover.jpg

 

[taurean]

Care to post your ammended code?