VBA URL to image (debug error)

[mickyflash]

H Guys - I have this code that deletes any images on the worksheet and then takes the url address in A2 and displays the image from the address 4 columns to the left.

Public Sub Add_Images_To_Cells()


ActiveSheet.Pictures.DELETE


    Dim lastRow As Long
    Dim URLs As Range, URL As Range
    
    With ActiveSheet
        lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        Set URLs = .Range("A2:A" & lastRow)
    End With


    For Each URL In URLs
        URL.Offset(0, 4).Select
        URL.Parent.Pictures.Insert URL.Value
        DoEvents
    Next
    
End Sub

This working (almost) in so much as the image is getting displayed, but I keep getting a debug error relating to the

URL.Parent.Pictures.Insert URL.Value line.

Can anyone see why Im having this error come up? Thanks in advance

 

[Domenic]

When the error occurs at that line, what is the value of URL.Value? Is it a valid path and filename? (Note: You can move your cursor over URL.Value to display the value.)

 

[mickyflash]

When the error occurs at that line, what is the value of URL.Value? Is it a valid path and filename? (Note: You can move your cursor over URL.Value to display the value.)

The URL.Value has a value but its not a path it seems to ="GK" (If the player selected is a Goalkeeper) ="CB" (if player is Centre Back)

 

[Domenic]

Your code expects Column A to contain URL addresses (ie. https://www.xyz.com/pictures/sample.jpg), and Pictures.Insert requires a valid address. Since "GK" and "CB" are not valid, you'll get an error. Does Column A contain valid URL addresses?

 

[mickyflash]

yes there is a valid URL in cell A2

EXAMPLE: http://premierleague-static-files.s3.amazonaws.com/premierleague/photos/players/110x140/p198849.png

 

[mickyflash]

Thanks for the advice I changed this line:

[COLOR=#333333]Set URLs = .Range("A2:A" & lastRow)[/COLOR]

to

[COLOR=#333333]Set URLs = .Range("A2")[/COLOR]

and it seems to be working now

 

[Domenic]

yes there is a valid URL in cell A2

While A2 contains a valid URL, it's likely that one or more cells in Column A do not, hence the error.

Thanks for the advice I changed this line:

Set URLs = .Range("A2:A" & lastRow)

to

Set URLs = .Range("A2")

and it seems to be working now

Yes, but isn't the intent to loop through each cell in Column A, instead of only A2 ?