Embedding images in looped sub for different worksheets

luzaroon Posts 1 Registration date Wednesday January 20, 2016 Status Member Last seen January 20, 2016 - Jan 20, 2016 at 04:57 AM
Hi there,

I've been using a macro at work that inserts images into one column, based on the values of cells in another column. This has served me quite well thus far. However, now I want to modify it so that the images are embedded. Also, as I now have multiple worksheets with different directories to acquire images from, I'm wondering if I can also specify worksheet, range and directory paths within one sub. I'm not great at coding but I've attempted modifications by referencing other people's examples, but I can't seem to get it right. Keep getting runtime errors 9 and 438, but unsure what there is to rectify.

Here is the original macro:

Sub InsertPic()

Dim CurrRow As Long
Dim FPrefix As String
Dim FPath As String
Dim CorrPic As String
Dim bContinue As Boolean

For CurrRow = 4 To 63
FPath = "C:\Users\luzaroon\Desktop\"
bContinue = True

FPrefix = ActiveSheet.Cells(CurrRow, 2).Value
If FPrefix = " " Then
Cells(CurrRow, 4).Select
End If

CorrPic = FPath + FPrefix + ".jpg"

With Cells(CurrRow, 4).Select
Set oFSo = CreateObject("Scripting.FileSystemObject")
If oFSo.fileExists(CorrPic) Then
With Selection.ShapeRange
.Left = Cells(CurrRow, 4).Left + 1
.Top = Cells(CurrRow, 4).Top
.LockAspectRatio = msoTrue
.Height = Cells(CurrRow, 4).Height / 1.07
End With

End If

End With

Next CurrRow

End Sub

Thanks in advance.