Check for Picture in a cell

[ssgmiami]

Each month I receive a spreadsheet from another office. From it I extract certain data. I have created a macro to loop thru and do this. The problem is, some cells contain both a picture and text. I need to ignore those cells.

I need a way to determine if a cell contains a picture. The only solution I can find on the web is more technical than my VB skills. If I can determine the cell has a picture, I can ignore it in my loop.

An alternative would be to just globally delete the contents all cells containing pictures prior to my loop. It is not a problem if I extract empty cells, just a problem if they contain a picture.

 

[Andrew Poulsom]

You can use the Picture's TopLeftCell property like this:

Sub Test()
    Dim Cell As Range
    Dim Pic As Picture
    For Each Cell In Selection
        For Each Pic In ActiveSheet.Pictures
            If Pic.TopLeftCell.Address = Cell.Address Then
                MsgBox "Cell " & Cell.Address(False, False) & " contains a picture"
            Else
                MsgBox Cell.Value
            End If
        Next Pic
    Next Cell
End Sub

 

[ssgmiami]

Andrew,

Thanks for the quick suggestion. I thought I could figure this out with a little boost. That doesn't seem to be the case. I would like to send a more fully detailed question along with a sample file. Can I do that, if so how?

John

 

[Andrew Poulsom]

You can certainly ask a more detailed question.

 

[ssgmiami]

OK, Here goes (this would be far simpler if I sent an actual sample)

The spread sheet I receive has a varying number of columns. Some columns represent sumarrized totals, I do not need those columns. Other colums contain raw data, I need those columns.

For Example:

All Regions Region 1 Peru Colombia Region 2 Belize Guatemala
300 200 125 75 100 50 50

In the small matrix above, the columns titled "All Regions", "Region 1" and "Region 2" contain summarized totals. I do not need them. I need the columns titled "Peru", "Colombia", "Belize" and Guatemala". They represent the raw data.

Each of the summarized colums has a small graphical arrow in the top left of the header cell denoting that the column contains summarized data. I need a way to identify the presence of this arrow and delete the column.

That way, when I am finished I will end up with a spreadsheet that looks like this:

Peru Colombia Belize Guatemala
125 75 50 50

As I said, it is a little more complicated than I originally stated. Does this make any sense? Can it be done?

Thanks,
John

 

[Andrew Poulsom]

What sort of object is the graphical arrow? A picture or a drawing object?

 

[ssgmiami]

I'm not sure I know the difference. Can I send you a small sample file?

 

[Andrew Poulsom]

Check your PMs.