title: Trying to find maximum value of string in range
body: <p>Okay, I have exhausted my feeble knowledge of Excel VBA coding and have not been able to figure out this solution.</p>
  
  <p>I have a single column range of values as part of a table.  The values in this column are groups of sequential strings such as:</p>
  
  <pre><code>ABC-001
  ABC-002
  XYZ-001
  EFDDGE-001
  ABC-003
  XYZ-002
  ABC-004
  </code></pre>
  
  <p>What I need to do is assign a value in the next row that is the next value in the whatever the group is.</p>
  
  <p>Example:</p>
  
  <ol>
  <li>If the next item is an "ABC" item, I need the value in the column to be <code>ABC-005</code></li>
  <li>If the next item is a "EFDDGE" item, I need the value in the column to be <code>EFDDGE-002</code>
  etc.</li>
  </ol>
  
  <p>The values in the column are in ascending order, by group (ie. <code>XYZ-005</code> would never be before <code>XYZ-003</code>).</p>
  
  <p>I have been working with the <code>Range.Find</code> method, but I can't seem to get it to work properly.</p>
  
  <p>Here is a screen-shot of a sample portion of the table:
  (Edit: apparently I can't post images since my "reputation is not a 10 or higher!)</p>
  
  <p>Try to picture this series of columns:</p>
  
  <p>[Next ID] [Item ID] [Description] [Disposition] [Original] [Final] [Weight]</p>
  
  <p>In answer to some of the questions:
  Yes, I am indeed trying to add the Item ID in the next available row in the table.</p>
  
  <p>The group portion of the "Item ID" is determined by a VLookup into the "LocationID" table related to the entries in the "Original" or "Final" columns, depending on the "Disposition" value.</p>
  
  <p>As you can see from the above screen shot, I have actually managed to implement a workable method of determining the "Next ID" and then having my VBA userform code copy that value and paste it into the adjacent "Item ID" column.  It works, but it is ugly.  Here is the bloated formula (take a deep breath):</p>
  
  <p>=IF(NOT(ISERROR(CONCATENATE(VLOOKUP(IF(NOT(ISBLANK(G13)),G13,F13),LocationID,2,FALSE),"-",TEXT(SUMPRODUCT((ItemID>VLOOKUP(IF(NOT(ISBLANK(G13)),G13,F13),LocationID,2,FALSE))<em>(ItemID&lt;=CONCATENATE(VLOOKUP(IF(NOT(ISBLANK(G13)),G13,F13),LocationID,2,FALSE),"-999")))+1,"000")))),(CONCATENATE(VLOOKUP(IF(NOT(ISBLANK(G13)),G13,F13),LocationID,2,FALSE),"-",TEXT(SUMPRODUCT((ItemID>VLOOKUP(IF(NOT(ISBLANK(G13)),G13,F13),LocationID,2,FALSE))</em>(ItemID&lt;=CONCATENATE(VLOOKUP(IF(NOT(ISBLANK(G13)),G13,F13),LocationID,2,FALSE),"-999")))+1,"000")))," ")</p>
  
  <p>So ... I have forced this to work, but I was thinking (hoping) that a bit of VBA code in the userform would obviate the need for such a messy method.</p>
  
  <p>BTW, thanks to all of you for even looking at this and for asking pertinent questions and offering possible solutions.
  -Bill</p>