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

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
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".

VBA Code:
        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:

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:
VBA Code:
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.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

adambc

Board Regular
Joined
Jan 13, 2020
Messages
177
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
@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?) ...

VBA Code:
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

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

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
(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).
 

Watch MrExcel Video

Forum statistics

Threads
1,130,174
Messages
5,640,601
Members
417,154
Latest member
gm_jagath

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