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

[adambc]

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 ...

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 ...

 

[Akuini]

Hmm, explaining a code in writing sometimes is harder than writing the code it self.

If you Split a text with "/" & the text has only one "/" then text become 2 parts. The UBound is the highest index which is 1 because it starts at 0.
z = Split("ABC0001/6", "/")
you get:
z(0) is ABC0001
z(1) is 6

so this part If UBound(z) = 1 Then is just to check if the string has only one "/", so if somehow your data has something like "ABC0001/6/12" or "ABC0001" then it won't be processed.

(why + 0?)

Split function create substring as string not as number, so in example above 6 is a string not number. In some cases the difference matters, but actually in this case after I tried it again it doesn't matter. So you can remove the "+ 0".

        If UBound(z) = 1 Then
            If d(z(0)) < z(1) Then d(z(0)) = z(1)
        End If

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

In dictionary object there’s Add method, but it will error if you try to add duplicate key.
Check this article:

Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery

This post contains everything you need to know about using the VBA Dictionary. There are tons of code examples including a real world application.

excelmacromastery.com

Here's some explanation in the article above:

dict.Add Key:="Orange", Item:=45

' This line gives an error as key exists already
dict.Add Key:="Orange", Item:=75


but without Add method, it works:

' Adds Orange to the dictionary
dict("Orange") = 45

' Changes the value of Orange to 100
dict("Orange") = 100

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

Correct.
But actually you need to replace "Private Sub ComboBox1_Change()" with this:

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

to check whether combobox1.value exists in the dictionary.

 

[adambc]

@Akuini

Brilliant, thank you, thank you ...

I tried removing the + 0 and answered my own UBound question late last night, but the d.add piece is really, really useful for something else I'm working on ...

Only in mockup mode at the moment, but I have 2 UserForms - first creates the new Check record, second creates an Issue record - still needs lots of work before it's workable - here's how I've adapted your code (thought you might like to see it - and it might help someone else in the future?) ...

Private Sub UserForm_Initialize()

Dim LastCheckRow As Long
Dim LastCheckID As String
Dim CheckNumber As String
Dim NextCheckNumber As String

LastCheckRow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
LastCheckID = Sheets("Sheet2").Cells(LastCheckRow, 1)

CheckNumber = Right(LastCheckID, 4)

NextCheckNumber = CheckNumber + 1

TextBox1.Text = "MOC" & Format(CStr(NextCheckNumber), "0000")

End Sub

Private Sub CommandButton1_Click()

Main1 = TextBox1
Main2 = TextBox2
Main3 = TextBox3

IssueForm.Show

End Sub

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) Then d(z(0)) = z(1)
        End If
    Next

If d.Exists("") Then d.Remove ""

TextBox1.value = Main1 & "/" & d(Main1) + 1
TextBox2 = Main2
TextBox3 = Main3

End Sub

Private Sub CommandButton1_Click()

Worksheets("Sheet1").Select

Dim lastrow1 As Long
lastrow1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Cells(lastrow1 + 1, 1).value = TextBox1
Cells(lastrow1 + 1, 2).value = TextBox2
Cells(lastrow1 + 1, 3).value = TextBox3
Cells(lastrow1 + 1, 4).value = TextBox4
Cells(lastrow1 + 1, 5).value = TextBox5

Worksheets("Sheet2").Select

Dim lastrow2 As Long
lastrow2 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Cells(lastrow2 + 1, 1).value = Main1

End Sub

 

[Akuini]

(why + 0?)

Split function create substring as string not as number, so in example above 6 is a string not number. In some cases the difference matters, but actually in this case after I tried it again it doesn't matter. So you can remove the "+ 0".

Sorry, I tried it again using different data, the difference between string and number does matter in this case.
So use the original code with "+ 0", it's to convert string to numeric.
Try it by adding "ABC0001/10" to the example data:
with "+ 0" it generates correct result i.e "ABC0001/11"
without "+ 0" it generates wrong result i.e "ABC0001/8" (because it's treated as string).