Embedding images in looped sub for different worksheets [Closed]

Report
Posts
1
Registration date
Wednesday January 20, 2016
Status
Member
Last seen
January 20, 2016
-
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
Else
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
ActiveSheet.Pictures.Insert(CorrPic).Select
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
Else

End If

End With

Next CurrRow

End Sub


Thanks in advance.

Cheers!

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!