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
M_TheJax Posts 1 Registration date Thursday March 5, 2015 Status Member Last seen March 5, 2015 - Mar 5, 2015 at 08:59 PM
Related:
- Excel vba insert picture from folder based on cell value
- Anvi folder locker reset key - Guide
- Based on the values in cells b77:b81, what function can automatically return the value in cell c77? ✓ - Excel Forum
- Number to words in excel formula without vba - Guide
- How to insert picture in word without moving text - Guide
- Excel vba create folder if not exist ✓ - Excel Forum
1 response
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 18, 2010 at 10:33 AM
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
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
Feb 19, 2010 at 06:36 AM
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.
Feb 19, 2010 at 06:47 AM
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.