VBA noob here, basic programming understanding
Background i have four columns N, P, R,S they have URLs of pictures in some of their cells, others are just blank. I need a way to insert the picture while keeping them in the same row (or labeled/linked somehow to their item number in column D on the same row. )
Found some code online (maybe this website?) that pulls a picture from a URL and puts it in the cell to the right. I added in a line ignoring errors to fix if there is a blank instead of url (im sure theres a better way to do this but i digress) Then i added a function to combine two different ranges (still need the other columns but figured get two working first). Worked except now everything is 9 cells lower than anticipated.... when i try to go in and adjust the position to be +or-9 i get an error.
Any help would be very appreciated.
PS (the data normally comes in columns together so N,O,P,Q but the code i found puts it in the adjacent cell on the right so to make it work i inserted blank columns. Ideally i would like something to read the first cell: if its a URL check the cell to the right, if blank paste image, if not check next cell. After it finds a blank cell an pastes and image Repeat checking for URL on the second cell in the row, if blank then move onto next row.
Sub InsertPic()
Dim pic As String 'file path of pic
Dim myPicture As Picture 'embedded pic
Dim rng1, rng2, Mrange As Range
Dim cl As Range 'iterator
Set rng1 = Range("o2
28") '<~~ Modify this range as needed. Assumes image link URL in column to the left
Set rng2 = Range("q2:q28")
Set Mrange = Union(rng1, rng2)
For Each cl In Mrange
pic = cl.Offset(0, -1)
Set myPicture = ActiveSheet.Pictures.Insert(pic)
'
'you can play with this to manipulate the size & position of the picture.
' currently this shrinks the picture to fit inside the cell.
With myPicture
.ShapeRange.LockAspectRatio = msoFalse
.Width = cl.Width
.Height = cl.Height
.Top = Rows(cl.Row).Top
.Left = Columns(cl.Column).Left
End With
'
On Error Resume Next
Next
End Sub
Background i have four columns N, P, R,S they have URLs of pictures in some of their cells, others are just blank. I need a way to insert the picture while keeping them in the same row (or labeled/linked somehow to their item number in column D on the same row. )
Found some code online (maybe this website?) that pulls a picture from a URL and puts it in the cell to the right. I added in a line ignoring errors to fix if there is a blank instead of url (im sure theres a better way to do this but i digress) Then i added a function to combine two different ranges (still need the other columns but figured get two working first). Worked except now everything is 9 cells lower than anticipated.... when i try to go in and adjust the position to be +or-9 i get an error.
Any help would be very appreciated.
PS (the data normally comes in columns together so N,O,P,Q but the code i found puts it in the adjacent cell on the right so to make it work i inserted blank columns. Ideally i would like something to read the first cell: if its a URL check the cell to the right, if blank paste image, if not check next cell. After it finds a blank cell an pastes and image Repeat checking for URL on the second cell in the row, if blank then move onto next row.
Sub InsertPic()
Dim pic As String 'file path of pic
Dim myPicture As Picture 'embedded pic
Dim rng1, rng2, Mrange As Range
Dim cl As Range 'iterator
Set rng1 = Range("o2
28") '<~~ Modify this range as needed. Assumes image link URL in column to the leftSet rng2 = Range("q2:q28")
Set Mrange = Union(rng1, rng2)
For Each cl In Mrange
pic = cl.Offset(0, -1)
Set myPicture = ActiveSheet.Pictures.Insert(pic)
'
'you can play with this to manipulate the size & position of the picture.
' currently this shrinks the picture to fit inside the cell.
With myPicture
.ShapeRange.LockAspectRatio = msoFalse
.Width = cl.Width
.Height = cl.Height
.Top = Rows(cl.Row).Top
.Left = Columns(cl.Column).Left
End With
'
On Error Resume Next
Next
End Sub
