VLOOUP Pictures

[asifmian55]

Friends:
I am creating a directory of our community. I have information about the families but I would like to add picture to this information so people can easily reconcile name with pictures.

In the attached spreadsheet I have three worksheets (1) MAIN DIRECTORY, (2) DIRECTORY and (3) PICTURES. I want to use a LOOKUP/INDEX or MATCH functions to get pictures from the PICTURES worksheet and paste them against the correct name in MAIN DIRECTORY.


Can anyone help me to sort this out. I tried many different ways but not successful.

Your help will be very highly appreciated.

[sintek]

I removed your merged cells in the directory cards...Steer clear from merged cells...They just cause havoc...
See if this is what you require...

[asifmian55]

This is absolutely fantastic. Thank you.

Is there any way that we can first delete the pics that are already there and then paste them again. The reason I am saying this is because if I add another name and pic to the directory and then run the macro, duplicate pics appear, i.e. same pics are pasted on top of the existing pics. Every time I run the macro, more pics are pasted.

Thank you so much for your help.

[Marc L]

This is my way where a same picture can't be on several people :

Sub Demo1() 
            Dim Rg As Range, Pict As Picture 
            Set Rg = Sheet1.[A1].End(xlDown) 
            Rg.Parent.Pictures.Delete 
            Rg.Parent.Activate 
            Application.ScreenUpdating = False 
    While Rg.Value2 > 0 
        Do 
            For Each Pict In Sheet3.Pictures 
                If Pict.TopLeftCell.Row = Rg.Value2 Then 
                         Pict.Copy 
                         Rg(4, 6).Select 
                         Rg.Parent.Paste 
                    With Selection 
                        .Left = Rg(4, 7).Left - .Width - 1 
                        .Top = Rg(7).Top - .Height - 1 
                    End With 
                         Exit For 
                End If 
            Next 
               Set Rg = Rg.End(xlToRight).End(xlToRight) 
        Loop While Rg.Value2 > 0 
            Set Rg = Cells(Rg.Row, 1).End(xlDown) 
    Wend 
            Set Rg = Nothing: Set Pict = Nothing 
            [A2].Select 
            Application.ScreenUpdating = True 
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[asifmian55]

Excellent!!
I could have never done even if I tried 100 times!!

[sintek]

This is my way where a same picture can't be on several people :

@ Marc L
Nice catch...didn't even notice...

[Marc L]

Thanks for the rep' sintek !

[asifmian55]

So how can we fix the problem of pasting pic over pic? Also can we put the array in a loop? I have more than 275 pics and respective names. The loop stops when end of names.

[sintek]

Marc L Solution solves...