Adding a loop to macro to insert pictures [Solved/Closed]

vba - Feb 18, 2010 at 10:06 AM - Latest reply: M_TheJax 1 Posts Thursday March 5, 2015Registration date March 5, 2015 Last seen
- 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
See more 

7 replies

Best answer
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Feb 18, 2010 at 10:33 AM
2
Thank you
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

Thank you, rizvisa1 2

Something to say? Add comment

CCM has helped 1671 users this month

rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen > vbayat 5 Posts Thursday February 18, 2010Registration date February 19, 2010 Last seen - 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.
vbayat 5 Posts Thursday February 18, 2010Registration date February 19, 2010 Last seen > rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Feb 19, 2010 at 03:15 PM
Hello, This now works. Thank you very much.
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!
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 28, 2014 at 08:50 AM
M_TheJax 1 Posts Thursday March 5, 2015Registration date March 5, 2015 Last seen - Mar 5, 2015 at 08:59 PM
I am essentially a novice w VBA for Excel. So far, so good BUT I just can't make my brain figure out something that has to be so simple....I need to do the opposite with the code you've shown. In other words, I need the path to the image to be what is collected from B6. I have the picture names. I just don't see what I'm doing wrong within the (). I used Dim path As String, then put the word path first in the brackets. I think it's the quotations within the brackets that are throwing me off.

This obviously doesn't work:

ActiveSheet.Pictures.Insert("path" & "image.jpg").Select
or
ActiveSheet.Pictures.Insert(path & "image.jpg").Select

Please have mercy.