# Selecting pictures

#### Raneis

##### New Member
Hi, I have a sheet full of chart pictures: 20 columns, each with four vertical picture.
I need to randomly select a single picture, but couldn't find the logic behind the serials attached to a picture by the system. I recorded a few macros, arbitrarily selecting a picture - but wasn't able to get the logic behind it.
Any suggestions? Thanks.

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### Ruddles

##### Well-known Member
Do you just need to know which cell each of your charts is located in? This code builds a table with that info in it. Be careful because it puts the results in column A, so change the value of chdata before you run it if you have anything already in column A:-

Code:
``````Option Explicit

Public Sub WhereChart()

Dim shp As Shape
Const chdata As String = [COLOR=#FF0000][B]"A"[/B][/COLOR]

Columns(chdata).ClearContents

For Each shp In ActiveSheet.Shapes
If shp.Type = msoChart Then
Cells(Cells(Rows.Count, chdata).End(xlUp).Row + 1, chdata) = "Chart """ & shp.Name & """ is in " & getcolumn(shp) & getrow(shp)
End If
Next shp
End Sub

Private Function getcolumn(ByVal shp As Shape) As String

Dim ic As Long

For ic = 1 To Columns.Count
If Cells(1, ic).Left > shp.Left Then
getcolumn = Replace(Replace(Cells(1, ic - 1).Address, "1", ""), "\$", "")
Exit Function
End If
Next ic

End Function

Private Function getrow(ByVal shp As Shape) As Long

Dim ir As Long

For ir = 1 To Rows.Count
If Cells(ir, 1).Top > shp.Top Then
getrow = ir - 1
Exit Function
End If
Next ir

End Function``````

#### Raneis

##### New Member
Thanks, but that wouldn't work for me. If you imagine the pictures as an array (4 rows by 20 columns), I know which picture I need to select however,
I don't know how the system designates it. I could not create the "exchange table" between my coordinates and the sustem's.

#### Ruddles

##### Well-known Member
You'd have to cycle through the pictures one by one and check which one is located at the row and column you're interested in.

#### Raneis

##### New Member
You'd have to cycle through the pictures one by one and check which one is located at the row and column you're interested in.[/Q

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,637
Messages
5,838,496
Members
430,551
Latest member
digitalbrolly

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back