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

 

[JGordon11]

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]

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]

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

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]

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.

Book1
A
B
C
D
E
1
ABC0001/1
Last CheckForm/Issue
Next Issue for ABC0002/11
Next Check Form Number
2
ABC0001/2
ABC0002/11
12
ABC0003
3
ABC0001/3
4
ABC0001/4
5
ABC0001/5
6
ABC0001/6
7
ABC0001/7
8
ABC0001/8
9
ABC0001/9
10
ABC0001/10
11
ABC0001/11
12
ABC0001/12
13
ABC0001/13
14
ABC0002/1
15
ABC0002/2
16
ABC0002/3
17
ABC0002/4
18
ABC0002/5
19
ABC0002/6
20
ABC0002/7
21
ABC0002/8
22
ABC0002/9
23
ABC0002/10
24
ABC0002/11
Sheet1
Cell Formulas
Range
Formula
D1
D1
="Next Issue for " & LastCkFm
C2
C2
=LastCkFm
D2
D2
=RIGHT(LastCkFm,LEN(LastCkFm)-8)+1
E2
E2
="ABC" & TEXT(MID(LastCkFm,4,4)+1,"0000")
Named Ranges
Name
Refers To
Cells
LastCkFm
=INDEX(Sheet1!$A$1:$A$10000,MAX((Sheet1!$A$1:$A$10000<>"")*ROW(Sheet1!$A$1:$A$10000)))
C2:E2, D1

 

[JGordon11]

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]

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

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

The file:

adambc - 1

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

 

[Akuini]

@adambc
Sorry, in the file "adambc - 1.xlsm" above there's Module1 Code with some codes inside, just delete it.

 

[adambc]

@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]

@Akuini

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