0
Private Sub CommandButton1_Click()

    With ComboBox1.Value
        Dim a, b As String
        Dim i, j, k, l, x, q, m, temp As Long
        a = ComboBox1.Text
        i = Application.WorksheetFunction.Match(a, Range("A1:A1000"), 0)
        j = Cells(i, 2)
        l = j * 1000
        For q = 2 To 100
            For m = 2 To 100
                If Cells(q, m).Value < 0 Then
                    k = m
                End If
            Next
        Next
        x = l
        If Cells(i, GC).Value = temp Then
            Click = Click + 1
        Else
            Click = 0
        End If
        Cells(i, GC) = x + Click
        TextBox1.Text = x + Click
        temp = Cells(i, GC).Value
    End With
    GC = GC + 1

it's a link of snapping what the problem of coming,

https://plus.google.com/100336580781932243792/posts/VHb18Hv6pTt?pid=6207264542909974946&oid=100336580781932243792

for more description

Above screen short presents the issue. what we want to achieve is that when a user select a value in column “A” the logic provides a value which contains predefined value derived from column “B” and a system generated serial number starting from “000”.

Say – When user selects from column “A” – PP , The system will get the corresponding value from column ”B” - 210 , and should also generate a serial number starting from “000”, and concatenate the value and should provide “210000” for the user.

When the user selects from column “A” – KK , , The system will get the corresponding value from column ”B” - 103 , and should also generate a serial number starting from “000”, and concatenate the value and should provide “103000” for the user.

When again the user selects column “A” – PP ,The system will get the corresponding value from column ”B” - 210 , and should also generate a serial number starting from “001”, and concatenate the value and should provide “210001” for the user. As the serial number ”000”, is already consumed.

0

Place this in UserForm1 module

Option Explicit

Private ur As Range

Private Sub CommandButton1_Click()
    Dim c As Range, co As Range

    Set c = getLastID
    Set co = c.Offset(, -1)

    If Len(co) > 0 Then c = co + 1 Else c = ur.Cells(c.Row, 2) * 1000
    Me.TextBox1.Text = c
End Sub

Private Sub ComboBox1_Change()
    Me.TextBox1.Text = getLastID.Offset(, -1)
End Sub

Private Function getLastID() As Range
    Dim sel As String, lc As Long, cr As Long

    sel = Me.ComboBox1.Text
    If Len(sel) = 0 Then sel = ur.Cells(ur.Row + ur.Rows.Count, 1)

    cr = Application.WorksheetFunction.Match(sel, ur.Columns(1), 0)
    lc = ur.Cells(cr, ur.Column + ur.Columns.Count + 1).End(xlToLeft).Column

    If lc < 5 Then lc = lc + 2
    Set getLastID = ur.Cells(cr, lc + 1)
End Function

Private Sub UserForm_Initialize()
    Set ur = Worksheets(1).UsedRange
End Sub

to generate a serial number starting from ID in column B * 1,000, or increment the last ID


enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.