• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Convert Pictures to Text or Value

G

Golfnut

New Member
I have a survey report in Excel 2010 which return a ’tick’ in picture form (pictures are in sequential form – Picture 1, Picture 2 etc).

I need to convert tick to text or value of some kind so that I can use Excel function ‘CountA’ to count the entries in each horizontal line.

This question has previously been answered on the Forum on July 10, 2013 by Narayank991 who suggested:

Code: 
Public Sub Replace_Picture()
Const Replace_Text = "OK"
Dim shp as Shape

For Each shp In ActiveSheet.Shapes
If shp.TopLeftCell.Address = shp.BottomRightCell.Address Then
shp.TopLeftCell.Value = Replace_Text
shp.Delete
End If
Next

Set shp = Nothing
End Sub

As I am a new learner in VB, I have the following questions:

1) How do I specify the range to use in my Excel Worksheet and where do I insert it in the listed code.

2) If I run the code above it shows a syntax error in line 2 - Const Replace_Text = "OK" How do I correct this syntax error?

Any help would be much appreciated.
 

Attachments

  • Sample file of Survey.pdf
    102.7 KB · Views: 5
Last edited by a moderator:
1) I don't think that you need to specify the range as it will loop through each shape of the sheet.

2) If you were taken the liberty to read the post irrespective of over-loop then might get the clue Const Replace_Text = "OK"

I wasn't user for the forum that time but assume it was changed due to some migration.

Where the ur xl?? here u uploaded PDF!
 
Deepak said:
1) I don't think that you need to specify the range as it will loop through each shape of the sheet.

2) If you were taken the liberty to read the post irrespective of over-loop then might get the clue Const Replace_Text = "OK"

I wasn't user for the forum that time but assume it was changed due to some migration.

Where the ur xl?? here u uploaded PDF!
Click to expand...

Deepak,
Many thanks for your prompt reply.
The code change you offer works perfectly.
The original code was copied and pasted directly from the previous post which is how it appeared, hence the mis-coding.
I tried to upload my Excel file (with and without the macro) by using the upload tab below. Although .xls and .xlsx files are listed it did not see them in my directory (windows file manager does). I therefore sent the Pdf so that the tick format could bee seen.

If you are able to tell me how to select the excel file it would be helpful.
Thanks agian for your response.
 
The uploading issue might occurred due to w10 meanwhile you may send the file to me for upload.
 
Last edited:
Deepak,
Thanks
FYI I'm using Windows 7 Pro x64.
No need to upload file as problem solved, thanks to you.
 
You must log in or register to reply here.
Back
Top