Rewrite Multiple Items in Spreadsheet

[tomgrandy]

I have a spreadsheet with multiple columns that copy the value in column L (Path_to_Primary_Image)

I need to rewrite those .jpg values in the Image_2, Image_3, and Image_4 columns to be

Image_2 = 1_1.jpg
Image_3 = 1_2.jpg
Image_4 = 1_3.jpg

And continue that renaming down the each of those three columns so

Path to Primary Image of 2.jpg would be renamed to:

Image_2 = 2_1.jpg
Image_3 = 2_2.jpg
Image_4 = 2_3.jpg

This would continue down each of the three columns until ALL 8.jpg have been renamed.

As always, your help is much appreciated.

Path_to_Primary_Image	Path_to_Image_2	Path_to_Image_3	Path_to_Image_4
sites/default/files/images/auctions/AUCTION_DATE/1.jpg	sites/default/files/images/auctions/AUCTION_DATE/1.jpg	sites/default/files/images/auctions/AUCTION_DATE/1.jpg	sites/default/files/images/auctions/AUCTION_DATE/1.jpg
sites/default/files/images/auctions/AUCTION_DATE/2.jpg	sites/default/files/images/auctions/AUCTION_DATE/2.jpg	sites/default/files/images/auctions/AUCTION_DATE/2.jpg	sites/default/files/images/auctions/AUCTION_DATE/2.jpg
sites/default/files/images/auctions/AUCTION_DATE/3.jpg	sites/default/files/images/auctions/AUCTION_DATE/3.jpg	sites/default/files/images/auctions/AUCTION_DATE/3.jpg	sites/default/files/images/auctions/AUCTION_DATE/3.jpg
sites/default/files/images/auctions/AUCTION_DATE/4.jpg	sites/default/files/images/auctions/AUCTION_DATE/4.jpg	sites/default/files/images/auctions/AUCTION_DATE/4.jpg	sites/default/files/images/auctions/AUCTION_DATE/4.jpg
sites/default/files/images/auctions/AUCTION_DATE/5.jpg	sites/default/files/images/auctions/AUCTION_DATE/5.jpg	sites/default/files/images/auctions/AUCTION_DATE/5.jpg	sites/default/files/images/auctions/AUCTION_DATE/5.jpg
sites/default/files/images/auctions/AUCTION_DATE/7.jpg	sites/default/files/images/auctions/AUCTION_DATE/7.jpg	sites/default/files/images/auctions/AUCTION_DATE/7.jpg	sites/default/files/images/auctions/AUCTION_DATE/7.jpg
sites/default/files/images/auctions/AUCTION_DATE/8.jpg	sites/default/files/images/auctions/AUCTION_DATE/8.jpg	sites/default/files/images/auctions/AUCTION_DATE/8.jpg	sites/default/files/images/auctions/AUCTION_DATE/8.jpg
sites/default/files/images/auctions/AUCTION_DATE/9.jpg	sites/default/files/images/auctions/AUCTION_DATE/9.jpg	sites/default/files/images/auctions/AUCTION_DATE/9.jpg	sites/default/files/images/auctions/AUCTION_DATE/9.jpg
sites/default/files/images/auctions/AUCTION_DATE/10.jpg	sites/default/files/images/auctions/AUCTION_DATE/10.jpg	sites/default/files/images/auctions/AUCTION_DATE/10.jpg	sites/default/files/images/auctions/AUCTION_DATE/10.jpg

 

[tomgrandy]

Tried the following but debug showed that:

"If InStr(1, Cells(r, "M"), ".jpg") > 0 Then":" was the problem.

Sub ReplaceChangeSecondaryImageJPGs()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False

    
'   Loop through all rows starting with row 3

'       Check to see if column M value has ".jpg" in it
        If InStr(1, Cells(r, "M"), ".jpg") > 0 Then
'           Replace value with column M
            Cells(r, "M").Value = Replace(Cells(r, "M").Value, ".jpg", Format(Cells(r, "M"), "_1.jpg"))

    End If
    
    
    Application.ScreenUpdating = True
    
    MsgBox "Image 2 Rewrite Complete!"
    
End Sub

 

[tomgrandy]

Tried a simpler method, but it did not change ".jpg" to "_1.jpg" though I did not get an error:

Sub modChangeSecondaryImageJPGs()

    Application.ScreenUpdating = False

    Columns("M").Replace What:=".jpg ", _
                            Replacement:="_1.jpg", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False
                            
                                
    Application.ScreenUpdating = True
    
    MsgBox "Secondary Image Rewrite Complete!"
    
    
End Sub

 

[tomgrandy]

This worked:

Sub modChangeSecondaryImageJPGs()

Range("M2:M70000").Replace What:=".jpg", Replacement:="_1.jpg"

Range("N2:N70000").Replace What:=".jpg", Replacement:="_2.jpg"

Range("O2:O70000").Replace What:=".jpg", Replacement:="_3.jpg"

End Sub