How to retrieve pictures from a dynamic table in another sheet when condition is met

[dobs]

Hello!

I am new to vba programming so any help I get would be really appreciated.
I am referring to Vijay's post "Using excel as a database-http://chandoo.org/wp/2012/04/02/using-excel-as-your-database/". I have one sheet(Sheet1) with a table with columns for Product, Region and Customer Type respectively and another sheet(Sheet2) with a table with two columns, one for Product name and one for Product Logo. This table will be dynamic.
When the product and its details are populated once the search criteria is met, I want to be able to fetch the corresponding product's images from Sheet2 and paste the image in the cell adjacent to the cell with the product name.
My understanding is a macro with a loop is needed to do this but as I am new to vba, I am not sure how to go about it.
______________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[Chihiro]

I'd suggest uploading your workbook with sample set up. Otherwise, it's difficult for anyone to help you.

 

[dobs]

Hi Chihiro,

Thanks for your note. I'm attaching the workbook "Product Inventory" here.
What I am trying to do, for e.g. In the "View" sheet, I select Accessories from the Products drop down and click on Show Data, all relevant data is populated. There is a column for Product Image(column D), where for all the populated values in the Product column (column E), I want to be able to retrieve the corresponding image from the sheet "Image" and size the image to fit in the respective cells. I have tried using INDEX and MATCH but have been unsuccessful. Your help is truly appreciated.

 

[Chihiro]

Hmm, so you intend to use Excel as Database code?

In that case, you need to first pull data and Pictures should not be inserted in middle of table columns (could be at the right most). Or it's going to cause issue with database code (as you can see in your sample file).

For the code portion, since it's going to be variable range. It's going to be bit tricky when compared to standard method to dynamically pull images.

For static range, you can use method outlined in link below.
http://exceltemplate.net/tutorial/how-to-display-images-dynamically-based-on-specific-cell-contents/

So by setting up first row with set up shown in link. You can copy down using code after data is pulled in.

Try it and see if you can get 1st row set up. I'll see if I have time this weekend to study and play with Database code and apply to your set up.

 

[dobs]

Hi Chihiro,

Apologies for replying late. Below listed are the things I have tried the past few days:
1) I have leveraged this code from another thread on this forum to find out the dynamic size of the table with the images.

Private Sub cmdUpdateProductLogos_Click()
Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim LogoLastRow As Long
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet
Dim rngCopy As Range
Dim rngPaste As Range

'Names of the worksheets
Set sourceSheet = Worksheets("Logos")
Set outputSheet = Worksheets("View")

'Determine last row of source
With sourceSheet
SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LogoLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
With outputSheet

'Determine last row in Column F
OutputLastRow = .Cells(.Rows.Count, "F").End(xlUp).Row

2) I used the method outlined in the link you provided and recorded a macro while doing so. Manually the process works but when I try running the macro, it fails at the Selection.Formula = "=ProductLogos"(highlighted in red below) step.

Sheets("Logos").Select
ActiveSheet.Range("C2").Select
'sourceSheet.Range("C2" & LogoLastRow).Select
Selection.Copy
Sheets("View").Select
'outputSheet.Range("I17" & OutputLastRow).Select
'ActiveSheet.Range("I17:I" & OutputLastRow).Select
Range("$I$17").Select
ActiveSheet.Pictures.Paste(Link:=True).Select
ActiveSheet.Shapes.Range(Array("Picture 1")).Select
'Selection.ShapeRange.ScaleWidth 1.4861878453, msoFalse, msoScaleFromTopLeft
'Selection.ShapeRange.IncrementLeft 2.25
'Selection.ShapeRange.IncrementTop 1.5
'Selection.ShapeRange.ScaleHeight 0.9552238806, msoFalse, msoScaleFromTopLeft
Application.CutCopyMode = False
ActiveWorkbook.Names.Add Name:="ProductLogos", RefersToR1C1:= _
"=INDEX(Logos!R2C3:R5C3,MATCH(View!R17C6:R18C6,Logos!R2C2:R5C2,0))"
ActiveWorkbook.Names("ProductLogos").Comment = ""
Selection.Formula = "=ProductLogos"

Any input from you will be greatly appreciated.

Thanks.

 

[Chihiro]

Do you have sample file with your set up and code? I'm guessing you are either missing Named Range or some other step. But will need to look at it in action.