+ Reply to Thread
Results 1 to 12 of 12

Find Data Within a Shape

  1. #1
    Registered User
    Join Date
    01-02-2018
    Location
    miami, usa
    MS-Off Ver
    10
    Posts
    6

    Find Data Within a Shape

    i have a workbook with 13 sheets. sheet1 contains a "find button" to search for data entered in cell M19 of the same sheet. sheets 2-13 contain the months of the year; in these sheets there are many shape (rectangles) and each shape has a name within it (12345 Smith, 45678 Jones, etc). i would like a macro which will search for either the number or name in a shape withing the 12 sheets with months of the year which is entered in cell M19 of sheet1. also when the data is found i would like for the sheet where the shape with the found data is located to be anchored at C1 R1.

    thanks
    Alex

  2. #2
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    5,878

    Re: Find Data Within a Shape

    Why are you putting data into rectangles rather than cells?
    Microsoft went to great efforts to write the program called Excel where data goes into cells and shapes are for making things pretty. Why are you wasting their efforts at writing routines to efficiently search cells for data?

    What do you mean by "anchored" at C1 R1?

    This macro might help you get started

    Please Login or Register  to view this content.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  3. #3
    Registered User
    Join Date
    01-02-2018
    Location
    miami, usa
    MS-Off Ver
    10
    Posts
    6

    Re: Find Data Within a Shape

    than you for your reply.
    i understand the purpose of Excel. I've been using spreadsheets since before Excel existed (VisiCalc). Unfortunately I never bother to learn VBA (my loss).
    The reason I'm using shapes is that I'm doing the scheduling for a wood shop and it's similar to a Gantt chart. I did not mean to insult the brilliant minds at Microsoft. After all, even though I'm 61, I still have hopes of Bill Gates adopting me.

  4. #4
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    5,878

    Re: Find Data Within a Shape

    I hope the code worked for you. Or pointed you in a good direction.

    What is the "anchored" bit? Upon further thought, my guess is that "to be anchored at C1 R1" means that $A$1 is the top left cell.

  5. #5
    Registered User
    Join Date
    01-02-2018
    Location
    miami, usa
    MS-Off Ver
    10
    Posts
    6

    Re: Find Data Within a Shape

    you got it. i want row 1 column 1 to always at top left.
    when i run the search the Sub FindShape()in the code turns yellow with the message box compile error: expected end sub

  6. #6
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    5,878

    Re: Find Data Within a Shape

    There is a line End Sub in that code. Did you copy the last line?

  7. #7
    Registered User
    Join Date
    01-02-2018
    Location
    miami, usa
    MS-Off Ver
    10
    Posts
    6

    Re: Find Data Within a Shape

    yes sir i did. by the way, thank for your time helping me

  8. #8
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    5,878

    Re: Find Data Within a Shape

    The posted code works for me. Somewhere you have a Sub declaration line without a matching End Sub.

  9. #9
    Registered User
    Join Date
    01-02-2018
    Location
    miami, usa
    MS-Off Ver
    10
    Posts
    6

    Re: Find Data Within a Shape

    below is the current code i'm using. it works great if the shape contains within it numbers or text but not both such as (123 smith or 456 Jones)

    Sub FindShape()
    Dim sht As Worksheet
    Dim shp As Shape
    For Each sht In ActiveWorkbook.Worksheets
    For Each shp In sht.Shapes
    If shp.TextFrame.Characters.Text = Worksheets("find").Range("m19").Value Then
    sht.Activate
    shp.Select
    ActiveWindow.ScrollRow = shp.TopLeftCell.Row
    ActiveWindow.ScrollColumn = shp.TopLeftCell.Column
    Exit Sub
    End If
    Next shp
    Next sht
    MsgBox "Sales Order Not Found.", vbInformation
    End Sub

  10. #10
    Valued Forum Contributor
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    760

    Re: Find Data Within a Shape

    If the text in the rectangle has a fixed/constant format, with a "space" for separating, use the 'Split' function to get separated parts of the name, e.g. for mikerickson's code:
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    01-02-2018
    Location
    miami, usa
    MS-Off Ver
    10
    Posts
    6

    Re: Find Data Within a Shape

    below is the current code i'm using. it works great if the shape contains within it numbers or text but not both such as (123 smith or 456 Jones)

    Sub FindShape()
    Dim sht As Worksheet
    Dim shp As Shape
    For Each sht In ActiveWorkbook.Worksheets
    For Each shp In sht.Shapes
    If shp.TextFrame.Characters.Text = Worksheets("find").Range("m19").Value Then
    sht.Activate
    shp.Select
    ActiveWindow.ScrollRow = shp.TopLeftCell.Row
    ActiveWindow.ScrollColumn = shp.TopLeftCell.Column
    Exit Sub
    End If
    Next shp
    Next sht
    MsgBox "Sales Order Not Found.", vbInformation
    End Sub

  12. #12
    Valued Forum Contributor
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    760

    Re: Find Data Within a Shape

    Your code:
    Please Login or Register  to view this content.
    it means more or less:
    Please Login or Register  to view this content.
    Use, as mikerickson wrote (in 'Sub test()' procedure), some sort of "equalizers" / "levellers":
    Please Login or Register  to view this content.
    e.g.:
    Please Login or Register  to view this content.
    Or (maybe) split the texts (TextInShape and TextInCell), using the 'Split' function, into two parts and only then prepare them for comparison.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1