asked on

Replace the Text in excel cells with Images

I have a sample attached file which has some 1500 housie tickets.

I would like to replace the numbers with the corresponding image for that number.

I have seperate image files or if required I can copy the images in a cell in excel. But there is no find and replace command from text to image.

the total numbers will range from 1 to 90 and corresponding 90 images also are ready.
Sample-excel.xlsx

tdlewis

The following function will choose the appropriate shape:

Function selectShape(v) As Shape
Dim found As Boolean
Dim s As Shape
Dim theRow As Variant

    Set selectShape = Nothing
    theRow = Application.VLookup(v, Sheets("Images").Range("A:A"), 1)
    If IsError(theRow) Then Exit Function
    
    found = False
    For Each s In ActiveSheet.Shapes
        If s.TopLeftCell.Row = theRow Then
            found = True
            Exit For
        End If
    Next s
    If found Then Set selectShape = s
End Function

Open in new window

dilipsjain

ASKER

Hi Tdlevis,

Thanks for the comment...

Actually I am very poor at programming etc. Would it be possible for you to put this function or so live in the excel sheet which I sent to you, so that It becomes possible... What I will do afterwards is copy all the 90 shapes to images sheet with appropriate numbers.

Would it be possible to do the necessary changes in the attached sheet...

It will be a great help to me...

ASKER CERTIFIED SOLUTION

tdlewis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dilipsjain

ASKER

Thanks Tdlevis...
This is what I wanted... Just One more request... This is replacing one number at a time. Is there any way where I could replace all the numbers together or may be looping the macro for all the numbers...

thank you so much :)

I accept this as my solution.. Just a final help needed.

tdlewis

Writing the code to replace all the numbers is slightly complicated by all the empty cells. Looking at column A implies there are three rows, but looking at column D one would only find two rows. Blank columns make it impossible to select the "Current Region" to get everything. (For example, the "Current Region" for cell A1 is A1:I3.)

Here are some options:
1. Before running the macro, select the cells to be replaced.
2. Name the region where the cells are to be replaced.
3. Hard code the region (currently it's A1:AC3

Are any of these options close to what you had in mind?

dilipsjain

ASKER

Thank u so much....