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 ...
Can anyone help please?
Many thanks ...
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 ...