use reference cell to insert a picture in a specific cell

[sully3868]

I have some VBA code here but I keep getting an error on line 11. I need the code to look at the text in the B column and then go to the specified folder and find the jpg file of the same name and then insert it into the excel sheet. This is a very simplified excel sheet, I need to change the code after for a different excel sheet which is much larger but the code should still work. Thanks.


Private Sub CommandButton1_Click()
Dim animal_pic As Pictures
Dim pic_location As String
Dim animal_name As String
For i = 2 To 6
animal_name = Worksheets("Sheet1").Cells(2, 2).Value
pic_location = "C:\Users\osullij2\Desktop\Animal Picture\" & Worksheets("Sheet1").Cells(2, 2).Value & ".jpg"

With Worksheets("Sheet1").Cells(i, 3)
Set animal_pic = ActiveSheet.Pictures.Insert(pic_location)

animal_pic.Top = .Top
animal_pic.Left = .Left
animal_pic.ShapeRange.LockAspectRatio = msoFalse
animal_pic.Placement = xlMoveAndSize
animal_pic.ShapeRange.Width = 170
animal_pic.ShapeRange.Height = 100
End With
Next
Worksheets("Sheet1").Cells(1, 1).Select
End Sub

 

[sully3868]

I'd love to say that fixed it but unfortunately it did not. Still only the tiger is showing + the error

 

[Fluff]

Have you checked for typos & extra spaces?

 

[sully3868]

Yes. I had a typo on the file name but I corrected that. Everything else seems to be good

 

[Fluff]

In that case I am not sure why you get the error as it works ok for me.

 

[sully3868]

you just changed the file locations and the names in the excel sheet and it worked fine for you?

 

[sully3868]

Just got it working there. One of the file names didn't match my excel sheet and the saved file, and this was giving me an error. Thanks for all the help @Fluff and @Candyman8019. )))))

 

[Fluff]

Glad you sorted it & thanks for the feedback.