Hi Excel Gurus,



I have a date field (dtDate) and a combobox field (cbxLocSName) (which will have a location short name) among various other fields in a data filling userform.
The format of the serial number is (Location short name + last 2 digits of the year + month + 3 digit serial number).

For ex.:

AAA1704001
AAA1704002
BBB1704001
BBB1704002

When a user changes the location short name or the date, then search the column "A" in the worksheet and find the last serial number for that particular combination of location short name, year and month
and increment it by 1 and show it another textbox (txtDocNo). So for LocSName AAA, the DocNo should show as AAA1704003

Also when the user selects a location short name for which there is no entry in the worksheet, then the DocNo should show as:

For ex.:

If the new location Short Name is CCC

Then the DocNo should show as CCC1704001

Any help on how to proceed with the above is much appreciated.

Expecting an early reply, thanks in advance.

Srinivas.

Got it working by searching through the range with a string and adding an (*) at the end of the string.

Srinivas

Just read your postings.

Would you be so kind to post your code for review ?

Thank you.

Yes for sure, below is my code

<Code>
Str = tRangeShort + Right(Year(dtDate.Value), 2) + Month(dtDate.Value)
txtDocNo.Text = GetIncrementalSerialNumber(NewRange, Str)

Private Function GetIncrementalSerialNumber(inRange As Range, sStr As String) As String
Dim StrSrch As String, tSerial As String, tNo As String
Dim tDigit As Integer, i As Integer
Dim tpRange As Range

StrSrch = sStr & "*" 'Concatenate an (*) at the end of the string
tSerial = ""
tNo = 0

For Each tpRange In inRange
If tpRange.Value Like StrSrch Then
tNo = tNo + 1 'Counter to find the number of exact matches
End If
Next tpRange
tNo = tNo + 1 'Adding 1 to total number of exact matches available

tDigit = 3 - Len(tNo) 'Find the length of the counter to prefix 0s
For i = 1 To tDigit
tSerial = "0" & tSerial 'Prefix 0s as required
Next i

GetIncrementalSerialNumber = sStr & tSerial & tNo 'Concatenate all
End Sub
<\Code>

Sorry I don't know how to post the codes within a box in this site.

and the last line of the code should be End Function and not End Sub.

Use HASH TAGS to embed code for display.

It will look like this when done accurately:

[ CODE]
your code
[ /CODE]

Thanks for the macro !

Thanks Logit...

how to make this thread solved....