Append Cell Data with .jpg

T

tomgrandy

New Member
Joined
May 10, 2024
Messages
41
Office Version
  1. 365
Platform
  1. MacOS
This may seem incredibly easy, but as a beginner, I could use some help writing a Macro with VBA.

I have several cells with a path written on the server and need to append the numbers at the end of the path with .jpg as shown below. There are about 450 in each column.

Humbly thanks,
Tom


Excel Formula: 
[TABLE]
[TR]
[TD]IMAGE_1[/TD]
[TD]IMAGE_2[/TD]
[TD]IMAGE_3[/TD]
[/TR]
[TR]
[TD]http://ai-drupal-10:8888/sites/default/files/feeds/auctions/images/04-02-24/1[/TD]
[TD]http://ai-drupal-10:8888/sites/default/files/feeds/auctions/images/04-02-24/1[/TD]
[TD]http://ai-drupal-10:8888/sites/default/files/feeds/auctions/images/04-02-24/1[/TD]
[/TR]
[TR]
[TD]http://ai-drupal-10:8888/sites/default/files/feeds/auctions/images/04-02-24/2[/TD]
[TD]http://ai-drupal-10:8888/sites/default/files/feeds/auctions/images/04-02-24/2[/TD]
[TD]http://ai-drupal-10:8888/sites/default/files/feeds/auctions/images/04-02-24/2[/TD]
[/TR]
[TR]
[TD]http://ai-drupal-10:8888/sites/default/files/feeds/auctions/images/04-02-24/3[/TD]
[TD]http://ai-drupal-10:8888/sites/default/files/feeds/auctions/images/04-02-24/3[/TD]
[TD]http://ai-drupal-10:8888/sites/default/files/feeds/auctions/images/04-02-24/3[/TD]
[/TR]
[TR]
[TD]http://ai-drupal-10:8888/sites/default/files/feeds/auctions/images/04-02-24/4[/TD]
[TD]http://ai-drupal-10:8888/sites/default/files/feeds/auctions/images/04-02-24/4[/TD]
[TD]http://ai-drupal-10:8888/sites/default/files/feeds/auctions/images/04-02-24/4[/TD]
[/TR]
[/TABLE]
 

Attachments

  • Screenshot 2024-09-24 at 1.19.51 PM.png
    Screenshot 2024-09-24 at 1.19.51 PM.png
    144.7 KB · Views: 4
Last edited:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Re: "as shown below"
Not readable to me.
I don't really know what you have in mind but the normal way would be
Code: 
.Value = .Value & ".jpg"
It looks like you want to skip certain cells but if you elaborate on that I am sure it will get sorted for you.
 
Upvote 0
jolivanes said:
Re: "as shown below"
Not readable to me.
I don't really know what you have in mind but the normal way would be
Code: 
.Value = .Value & ".jpg"
It looks like you want to skip certain cells but if you elaborate on that I am sure it will get sorted for you.
Click to expand...
Thanks for your quick reply!

I have a spreadsheet with several macros running in it. I need another macro that will take all the paths in column I, J, K and L from lines 3-450 and change values like:


to


From there I have a macro written that will change the values to 1_1.jpg or 1_2.jpg

What I don't understand is now to simply add .jpg to all the paths/values in column I J K and L which look like this:

 
Upvote 0
Something like this?

Sub InsertStuffAfter()
Dim cell
For Each cell In Selection
cell.Value = cell.Value & ".jpg"
Next cell
End Sub

But not sure how to tell it to do the entire column and then move to the next column or do all columns.
 
Upvote 0
This works, but it seems jenky to me for a number of reasons.

1) I don't know how many items there will be on each worksheet to add .jpg to and if I give a value like I did for 500, it fills in the empty cells with .jpg as well.

2) There has to be an easier way than running it through each column. But if that is the best method, it works.

Thanks in advance and sorry about my ignorance - just learning this.

VBA Code: 
Sub URLAddJPG()

    Application.ScreenUpdating = False

Dim cell

For Each cell In Range("i2:i500")

cell.Value = cell.Value & ".jpg"

Next cell

For Each cell In Range("j2:j500")

cell.Value = cell.Value & ".jpg"

Next cell

For Each cell In Range("k2:k500")

cell.Value = cell.Value & ".jpg"

Next cell

For Each cell In Range("l2:l500")

cell.Value = cell.Value & ".jpg"

Next cell

    Application.ScreenUpdating = True
   
    MsgBox "Added .JPG to Image URL Complete!"
   
End Sub
 
Upvote 0
Re: "move to the next column or do all columns"
How do you determine that? Just ALL columns? No columns with data that does not need to be included?
No cells in the column(s) that need to be exempt, like cells that don't start with "http://"?

It assumes that there are no headers, all columns the same length, Row 1 is the deciding row on the amount of columns to do.
Code: 
Sub With_Loops()
Dim lc As Long, lr As Long, i As Long, j As Long
lc = Cells(1, Columns.Count).End(xlToLeft).Column
lr = Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
Application.ScreenUpdating = False
    For j = 1 To lc
        For i = 1 To lr
            Cells(i, j).Value = Cells(i, j).Value & ".jpg"
        Next i
    Next j
Application.ScreenUpdating = True
End Sub

Just saw your last post. Will change above to suit (I hope).

Is Column I (the 9th Column) the column to start from?
 
Upvote 0
jolivanes said:
Re: "move to the next column or do all columns"
How do you determine that? Just ALL columns? No columns with data that does not need to be included?
No cells in the column(s) that need to be exempt, like cells that don't start with "http://"?

It assumes that there are no headers, all columns the same length, Row 1 is the deciding row on the amount of columns to do.
Code: 
Sub With_Loops()
Dim lc As Long, lr As Long, i As Long, j As Long
lc = Cells(1, Columns.Count).End(xlToLeft).Column
lr = Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
Application.ScreenUpdating = False
    For j = 1 To lc
        For i = 1 To lr
            Cells(i, j).Value = Cells(i, j).Value & ".jpg"
        Next i
    Next j
Application.ScreenUpdating = True
End Sub

Just saw your last post. Will change above to suit (I hope).

Is Column I (the 9th Column) the column to start from?
Click to expand...
Yes, the columns are I, J, K and L

The headers are I=IMAGE_1, J-IMAGE_2, K=IMAGE_3, L=IMAGE_4
 
Upvote 0
@jolivanes - I take that back - unsure why it looked like it worked the first time.

It is changing the values in Column A with .jpg, but not columns I, J, K and L
 
Upvote 0
Starts in Row 2 of every column from Column I to last column calculated from the 2nd row.
Code: 
Sub With_Loops_version2()
Dim lc As Long, lr As Long, i As Long, j As Long
lc = Cells(2, Columns.Count).End(xlToLeft).Column    '<---- If hard coded, see 2 lines down, comment this line out
Application.ScreenUpdating = False
    For j = 9 To lc    '<---- For j = 9 To 12 if hard coded
        lr = Cells(Rows.Count, j).End(xlUp).Row
        For i = 2 To lr
            Cells(i, j).Value = Cells(i, j).Value & ".jpg"
        Next i
    Next j
Application.ScreenUpdating = True
End Sub

You can hard code the Columns I to L but I wouldn't if not required.
 
Upvote 0
Solution
jolivanes said:
Starts in Row 2 of every column from Column I to last column calculated from the 2nd row.
Code: 
Sub With_Loops_version2()
Dim lc As Long, lr As Long, i As Long, j As Long
lc = Cells(2, Columns.Count).End(xlToLeft).Column    '<---- If hard coded, see 2 lines down, comment this line out
Application.ScreenUpdating = False
    For j = 9 To lc    '<---- For j = 9 To 12 if hard coded
        lr = Cells(Rows.Count, j).End(xlUp).Row
        For i = 2 To lr
            Cells(i, j).Value = Cells(i, j).Value & ".jpg"
        Next i
    Next j
Application.ScreenUpdating = True
End Sub

You can hard code the Columns I to L but I wouldn't if not required.
Click to expand...

That's the winner!!! Thank you so much!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Rewrite Multiple Items in Spreadsheet
Replies
3
Views
128
tomgrandy
T
T
  • Solved
VBA Find and Replace Part of a URL
2
Replies
11
Views
686
Akuini
Akuini
D
  • Solved
Count total award value per unique supplier
Replies
4
Views
89
Fluff
Fluff
T
  • Question Question
VBA Replace Based on Data from Cells in Another Column
Replies
2
Views
237
Burrgogi
Burrgogi
C
  • Solved
change data depending on the date
Replies
8
Views
518
etaf
etaf

Forum statistics

Threads
1,223,879
Messages
6,175,145
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top