Adding a loop to macro to insert pictures

Solved/Closed
vba - Feb 18, 2010 at 10:06 AM
M_TheJax
Posts
1
Registration date
Thursday March 5, 2015
Status
Member
Last seen
March 5, 2015
- Mar 5, 2015 at 08:59 PM
Hello,
Could someone please help me in writing a loop in this macro in excel :
I have an excel sheet where it contains the name of the pictures in column B. I have written a Macro to pick up the name of the picture in column B and insert the actual picture from directory into column A.
I need create a loop to do this depending on the number of pictures in the column B. The number of rows varies from 10 to 1000.
The macro that I have written works perfect for the first row but does not go to the next row since I am not sure how to do it.

Thanks for your help in advance.
Vida


Please see the macro that I’ve written:-

Sub Picture()
Range("A6").Select 'This is where picture will be inserted
Dim picname As String
picname = Range("B6") 'This is the picture name
ActiveSheet.Pictures.Insert("C:\Users\vbayat\My Documents\vidabayat\re-market\" & picname & ".jpg").Select 'Path to where pictures are stored
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This resizes the picture
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
With Selection
.Left = Range("A6").Left
.Top = Range("A6").Top
.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.Height = 100#
.ShapeRange.Width = 80#
.ShapeRange.Rotation = 0#
End With

Range("A10").Select
Application.ScreenUpdating = True

Exit Sub

ErrNoPhoto:
MsgBox "Unable to Find Photo" 'Shows message box if picture not found
Exit Sub
Range("B20").Select

End Sub

1 reply

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Feb 18, 2010 at 10:33 AM
I have not tested your macro, but for looping you can try some thing like this

Assumptions
1. picture names are found in col B starting at B2
2. You want to paste each picture at different location
3. where to paste the picture identified in col B, is found in col C starting at C2


Sub Picture()
 Dim picname As String
 
 Dim pasteAt As Integer
 Dim lThisRow As Long
 
    lThisRow = 2
 
    Do While (Cells(lThisRow, 2) <> "")
    
        'Range("A6").Select 'This is where picture will be inserted
        pasteAt = Cells(lThisRow, 3)
        Cells(pasteAt, 1).Select 'This is where picture will be inserted
        
        'Dim picname As String
        'picname = Range("B6") 'This is the picture name
         picname = Cells(lThisRow, 2) 'This is the picture name
         
        ActiveSheet.Pictures.Insert("C:\Users\vbayat\My Documents\vidabayat\re-market\" & picname & ".jpg").Select 'Path to where pictures are stored
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''
        ' This resizes the picture
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''
        With Selection
            '.Left = Range("A6").Left
            '.Top = Range("A6").Top
            .Left = Cells(pasteAt, 1).Left
            .Top = Cells(pasteAt, 1).Top
            
            .ShapeRange.LockAspectRatio = msoFalse
            .ShapeRange.Height = 100#
            .ShapeRange.Width = 80#
            .ShapeRange.Rotation = 0#
        End With
        
        lThisRow = lThisRow + 1
    
    Loop
    
    Range("A10").Select
    Application.ScreenUpdating = True
    
    Exit Sub
    
ErrNoPhoto:
    MsgBox "Unable to Find Photo" 'Shows message box if picture not found
    Exit Sub
    Range("B20").Select

End Sub
2
vbayat
Posts
5
Registration date
Thursday February 18, 2010
Status
Member
Last seen
February 19, 2010

Feb 19, 2010 at 06:36 AM
Thanks for your input. Your assumption is right except I always need to insert the pictures on the same row but column A. So the name of the picture is found in Column.B2 and would like to insert the image into Col.A2. I ran your macro and unfortunately it errors at

Cells(pasteAt, 1).Select 'This is where picture will be inserted

And I cannot fix it. I would be grateful for your help.

Thanks in advance.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769 > vbayat
Posts
5
Registration date
Thursday February 18, 2010
Status
Member
Last seen
February 19, 2010

Feb 19, 2010 at 06:47 AM
Are you saying that on a sheet
1. On cell b2 would be the picture name
2. on cell a2 you would insert the picture

So the row where the name is, is the row where the picture is going to be pasted ?
If that is the case


Change this line
pasteAt = Cells(lThisRow, 3)
to

pasteAt = lThisRow

See if it works for you. Also please be aware that there cannot be a blank value in col B as the macro will terminate its execution as soon as it encounters such a cell.
0
vbayat
Posts
5
Registration date
Thursday February 18, 2010
Status
Member
Last seen
February 19, 2010
> rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022

Feb 19, 2010 at 03:15 PM
Hello, This now works. Thank you very much.
0
Is there a way to alter the code so you can have a blank or a code picture name it cannot find.

Also, normally all the cells are squished together really tightly. Now that we are adding a picture they will become a bit broader. Is there a way for the cell height and width to auto adjust with that of the picture. Thank you so much!
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Jun 28, 2014 at 08:50 AM
0