+ Reply to Thread
Results 1 to 8 of 8

Named Range of strings find first two digits in each

  1. #1
    Registered User
    Join Date
    10-20-2014
    Location
    The Netherlands
    MS-Off Ver
    2007
    Posts
    29

    Named Range of strings find first two digits in each

    Dear EF users,

    Unfortunately I am unable to upload the whole code, but can simplify it descriptively:

    I have a named range (ProductCode) of 100 rows and 1 column. Each cell (100 in total) is a string of the previous three cells:
    Please Login or Register  to view this content.
    Within a userform, more specifically a combobox (cboProductCode) there is a value which is searched for in this range:

    Please Login or Register  to view this content.
    This means that cell M60 becomes the value of column A and row x, whereby row x is the first row in the named range where PC is found.

    The problem I have is that I only would like to search in the first two digits of each row of the named range. These are a kind of identifier, if you wish. If the list is the following, it doesn't work. For example:

    01 temp abc10
    02 temp bcd11
    03 temp cde12
    " " "
    10 temp jkl20

    If PC = 10, then the search function returns 01 (the first column), because within that row it has found "10". If it only looks at the first two digits, then the result should be the 10th row.

    I've tried looking at row A, but have run-time error 91 which I am unable to solve. I've tried Left(x, 2), but don't know how to correctly implement that in such a search. Does anyone have any smart solution for this?

    Thanks a million!
    Last edited by ChrisPatterson; 07-19-2019 at 09:37 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    11-02-2016
    Location
    NY
    MS-Off Ver
    2010
    Posts
    459

    Re: Named Range of strings find first two digits in each

    There's not a lot to go on here, esp. without a solid example. But you could try to parse the data, to help isolate the 1st two characters and the last two characters. Or use a helper column that extracts the info that you need.

  3. #3
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: Named Range of strings find first two digits in each

    implement this method - you should see results in intermediate window
    Please Login or Register  to view this content.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Named Range of strings find first two digits in each

    You could use Match to get the row within the named range.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  5. #5
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: Named Range of strings find first two digits in each

    this seems to be ok
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    10-20-2014
    Location
    The Netherlands
    MS-Off Ver
    2007
    Posts
    29

    Re: Named Range of strings find first two digits in each

    Hi Nigelog,
    Thanks for the help. Unfortunately this doesn't work with a Private Sub.

    Please Login or Register  to view this content.
    Can't have the "Option Explicit" inside here and, if I remove that, I then get an "Expected End Sub". Adding an "End Sub" doesn't change this.

  7. #7
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: Named Range of strings find first two digits in each

    @ Chris
    this was only a few pointers to try to get past the search anomoly of returning wrong rows, not a solution for your actual code

    Try Nories solution as it is probably spot on

  8. #8
    Registered User
    Join Date
    10-20-2014
    Location
    The Netherlands
    MS-Off Ver
    2007
    Posts
    29

    Re: Named Range of strings find first two digits in each

    Thanks Norie, your code worked a treat!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. find letter and position in the matrix of the 10 strings of 6 digits.
    By jorel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-24-2018, 12:16 PM
  2. [SOLVED] Syntax to find the row num of a range of strings
    By dmcgov in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-05-2018, 03:20 PM
  3. Find two strings in a range select cells in between and loop through range
    By maxwell13 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-27-2018, 04:04 AM
  4. [SOLVED] Find the strings of only the last digits of the numbers in the 5 sectors, please!
    By jorel in forum Excel Programming / VBA / Macros
    Replies: 41
    Last Post: 04-16-2017, 08:46 AM
  5. [SOLVED] Set Range based on FIND of multiple strings
    By delaing in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-14-2016, 12:18 PM
  6. Is it possible to find the min of the last few digits of a range.
    By Odlanier in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-09-2014, 10:38 AM
  7. Find Unique Text Strings in Range
    By AlvaroSiza in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-28-2011, 10:08 AM

Tags for this Thread

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