How to find last value in a string (let me explain) ...

adambc

Board Regular
Joined
Jan 13, 2020
Messages
177
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I have a requirement that would probably be best suited to a database, but I don't have any database knowledge and the volumes don't warrant finding someone who does (I a volunteer and we don't have any volunteers who know databases) ...

We carry out spot (clear desk) checks after everyone has left for the day - each check results in n issues which is what we need to record/track - I need to approach this with UserForms and have built a CheckForm (that records new checks) and an IssueForm that is called from the CheckForm to add an issue - the numbering I want to use is ABCxxxx/x (starting at ABC0001/1) where the Check is ABC0001 and the associated Issues are /1, /2, /3 etc ... hope that makes sense?

From a previous piece of work I have what I need to increment the Check number ie ABC0001, but I'm struggling to find a way to find the last Issue number - here's my starting point (based on the previous approach), but it depends on the latest Issue number being on the last row ...

VBA Code:
Private Sub UserForm_Initialize()

Dim LastCheckRow As Long
Dim LastCheckNumber As String
Dim CheckNumber As String
Dim IssueNumber As Integer
Dim NextIssueNumber As Integer

'Check Number format is ABCxxxx/x

LastCheckRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
LastCheckNumber = Sheets("Sheet1").Cells(LastCheckRow, 1)

'MsgBox LastCheckNumber

CheckNumber = Left(LastCheckNumber, 8)

'MsgBox CheckNumber

IssueNumber = Right(LastCheckNumber, 1)

'MsgBox IssueNumber

NextIssueNumber = IssueNumber + 1

'MsgBox NextIssueNumber

'MsgBox CheckNumber & NextIssueNumber

TextBox1.Text = CheckNumber & NextIssueNumber

End Sub

Can anyone help please?

Many thanks ...
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

JGordon11

Board Regular
Joined
Jan 18, 2021
Messages
67
Office Version
  1. 2019
Platform
  1. Windows
looks like CheckNumber should equal Left(LastCheckNumber, 7) unless you want to include the /.

Since CheckNumber will always be length 7, and adding 1 for the slash, you can use IssueNumber= Right(LastCheckNumber,Len(LastCheckNumber)-8)
 

adambc

Board Regular
Joined
Jan 13, 2020
Messages
177
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
looks like CheckNumber should equal Left(LastCheckNumber, 7) unless you want to include the /.

Since CheckNumber will always be length 7, and adding 1 for the slash, you can use IssueNumber= Right(LastCheckNumber,Len(LastCheckNumber)-8)
OK, another way to get at the IssueNumber, but I need to know what the last IssueNumber used was ie was it ABC0001/1, or ABC0001/2, or ABC0001/3, or ABC0001/n - and then increment it ie to ABC0001/n+1
 

adambc

Board Regular
Joined
Jan 13, 2020
Messages
177
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I have a requirement that would probably be best suited to a database, but I don't have any database knowledge and the volumes don't warrant finding someone who does (I a volunteer and we don't have any volunteers who know databases) ...

We carry out spot (clear desk) checks after everyone has left for the day - each check results in n issues which is what we need to record/track - I need to approach this with UserForms and have built a CheckForm (that records new checks) and an IssueForm that is called from the CheckForm to add an issue - the numbering I want to use is ABCxxxx/x (starting at ABC0001/1) where the Check is ABC0001 and the associated Issues are /1, /2, /3 etc ... hope that makes sense?

From a previous piece of work I have what I need to increment the Check number ie ABC0001, but I'm struggling to find a way to find the last Issue number - here's my starting point (based on the previous approach), but it depends on the latest Issue number being on the last row ...

VBA Code:
Private Sub UserForm_Initialize()

Dim LastCheckRow As Long
Dim LastCheckNumber As String
Dim CheckNumber As String
Dim IssueNumber As Integer
Dim NextIssueNumber As Integer

'Check Number format is ABCxxxx/x

LastCheckRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
LastCheckNumber = Sheets("Sheet1").Cells(LastCheckRow, 1)

'MsgBox LastCheckNumber

CheckNumber = Left(LastCheckNumber, 8)

'MsgBox CheckNumber

IssueNumber = Right(LastCheckNumber, 1)

'MsgBox IssueNumber

NextIssueNumber = IssueNumber + 1

'MsgBox NextIssueNumber

'MsgBox CheckNumber & NextIssueNumber

TextBox1.Text = CheckNumber & NextIssueNumber

End Sub

Can anyone help please?

Many thanks ...
Apologies if this is in the wrong place ...

Let's use ABCxxxx/y as the format of a Check/Issue Number ...

Could I write a sub ...

- filter the Check/Issue Number column on ABCxxxx* (CheckNumber) <<< it's these two rows I don't know how to do???
- find the maximum value of y in the filtered list (IssueNumber) <<< it's these two rows I don't know how to do???
- NextIssueNumber = IssueNumber + 1
- TextBox1.Value = CheckNumber & "/" & NextIssueNumber

Thoughts???
 

JGordon11

Board Regular
Joined
Jan 18, 2021
Messages
67
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

I'm not exactly clear on what your trying to do so this may or may not help. The named Range LastCkFm (formula below) assumes your CheckForm numbers are in column A and are less than or equal to 10000 rows. No other data can be in column A below the checkform numbers, or that named range technique will not work.
Rich (BB code):
Book1
ABCDE
1ABC0001/1Last CheckForm/IssueNext Issue for ABC0002/11Next Check Form Number
2ABC0001/2ABC0002/1112ABC0003
3ABC0001/3
4ABC0001/4
5ABC0001/5
6ABC0001/6
7ABC0001/7
8ABC0001/8
9ABC0001/9
10ABC0001/10
11ABC0001/11
12ABC0001/12
13ABC0001/13
14ABC0002/1
15ABC0002/2
16ABC0002/3
17ABC0002/4
18ABC0002/5
19ABC0002/6
20ABC0002/7
21ABC0002/8
22ABC0002/9
23ABC0002/10
24ABC0002/11
Sheet1
Cell Formulas
RangeFormula
D1D1="Next Issue for " & LastCkFm
C2C2=LastCkFm
D2D2=RIGHT(LastCkFm,LEN(LastCkFm)-8)+1
E2E2="ABC" & TEXT(MID(LastCkFm,4,4)+1,"0000")
Named Ranges
NameRefers ToCells
LastCkFm=INDEX(Sheet1!$A$1:$A$10000,MAX((Sheet1!$A$1:$A$10000<>"")*ROW(Sheet1!$A$1:$A$10000)))C2:E2, D1
 

JGordon11

Board Regular
Joined
Jan 18, 2021
Messages
67
Office Version
  1. 2019
Platform
  1. Windows
Also the Named range assumes the data are on a Sheet called Sheet1. You'll need to change the Sheet name, column ID, and max rows in the named range formula to fit your needs.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try this:
Select an item in the combobox
The textbox will show a new IssueNumber

VBA Code:
Dim d As Object

Private Sub ComboBox1_Change()
TextBox1.Value = ComboBox1.Value & "\" & d(ComboBox1.Value) + 1
End Sub

Private Sub UserForm_Initialize()
Dim va, z, x

With Sheets("Sheet1")
    va = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
End With

Set d = CreateObject("scripting.dictionary")
d.CompareMode = vbTextCompare

    For Each x In va
        z = Split(x, "/")
        If UBound(z) = 1 Then
            If d(z(0)) < z(1) + 0 Then d(z(0)) = z(1) + 0
        End If
    Next
If d.Exists("") Then d.Remove ""

ComboBox1.List = d.keys

End Sub

adambc - 1.jpg



The file:
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
@adambc
Sorry, in the file "adambc - 1.xlsm" above there's Module1 Code with some codes inside, just delete it.
 

adambc

Board Regular
Joined
Jan 13, 2020
Messages
177
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
@Akuini

This is looking great, thank you ...

I do need to adapt it to a background sub (rather than interactive), but I don't fully understand how it works - in particular how d sets the next y value for ABCxxxx (using format ABCxxxx/y? ...

Would it be possible to add a commentary so that I can play with the code please?

I don't get why UBound(z) always =1 (I was expecting = y) - then I get really lost!!!

Many thanks ...
 

adambc

Board Regular
Joined
Jan 13, 2020
Messages
177
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
IGNORE MY PREVIOUS POST - I think(?) I get it!!!

Still not quite sure why UBound(z) = 1 - is it because UBound returns the POSITION rather than the VALUE of the upper dimension?

But ...

d(z(0)) gives me the ITEM for the KEY (ABCxxxx)

... so the test is ...

If ITEM < y + 0 then ITEM = y + 0 (why + 0?)

... which means ITEM is the largest value of y in the range

OK so far!!!

ComboBox1.List = d.keys ...

How does this work - does d store KEYS/ITEMS in the background (there is no d.add)?

And then to ...

TextBox1.Value = ComboBox1.Value & "\" & d(ComboBox1.Value) + 1

d(ComboBox1.Value) gets the ITEM for the KEY ComboBox1.Value (which is the largest value of y in the range

+1 and BINGO

Have I got it right (your help with my 2 outstanding questions would be appreciated?) ...

Many thanks (I'll mark your post as solution when I've heard back from you in case I haven't got it quite right) ...
 

Watch MrExcel Video

Forum statistics

Threads
1,130,425
Messages
5,642,042
Members
417,251
Latest member
Dordrecht

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top