Excel: How do I count the number of pictures in an excel cell?

[Guest]

I have access to publically available data that contains "ticks" in cells. I
can copy the data across to Excel and the ticks appear as pictures, all
neatly arranged in the appropriate cell. One tick - one picture, two ticks -
two pictures, there can be up to three ticks / pictures per cell. I can count
them and enter the number of ticks manually but this is time consuming and
liable to mistakes.

Any suggestions?

 

[Bernie Deitrick]

John,

The first macro below will put the count into the column to the right of the cells with the shapes,
and leave the shapes alone. The second macro will put the count into the cell with the shapes, and
remove the shapes.

HTH,
Bernie
MS Excel MVP

Sub CountShapes()
Dim mySh As Shape

For Each mySh In ActiveSheet.Shapes
mySh.TopLeftCell.Value = mySh.TopLeftCell.Value + 1
Next
End Sub

Sub CountAndRemoveShapes()
Dim mySh As Shape

For Each mySh In ActiveSheet.Shapes
mySh.TopLeftCell.Value = mySh.TopLeftCell.Value + 1
mySh.Delete
Next
End Sub