当前位置: 动力学知识库 > 问答 > 编程问答 >

excel - Find Roll and Column of Name with latest Date

问题描述:

I have a table with three columns,

ID, Name and Date

then I create a userform with textbox ID and Name.

how could I display the Name of similar ID from the table with latest Date when I key in the ID in the userform? (similar ID will have different names, but I want to display the one with latest date in the table)

thanks in advance for all the help

coding for the textbox1

Private Sub TextBox1_Change()

getdata

End Sub

coding for the getdata module

Sub getdata()

If IsNumeric(UserForm1.TextBox1.Value) Then

flag = False

i = 0

id = UserForm1.TextBox1.Value

Do While Cells(i + 1, 1).Value <> ""

If Cells(i + 1, 1).Value = id Then

flag = True

For j = 2 To 3

UserForm1.Controls("textbox" & j).Value = Cells(i + 1, j).Value

Next j

End If

i = i + 1

Loop

If flag = False Then

For j = 2 To 3

UserForm1.Controls("TextBox" & j).Value = ""

Next j

End If

Else

ClearForm

End If

End Sub

网友答案:

This should do it for you. This routine goes in your userform code module:

Private Sub TextBox1_AfterUpdate()
    TextBox2 = Evaluate("=INDEX(B2:B999,MATCH(MAX((IF(A2:A999=" & TextBox1 & _
                        ",1)*(C2:C999)),1),IF(A2:A999=" & TextBox1 & _
                        ",1)*(C2:C999),))")
End Sub

It assumes your data are in columns A, B, and C. It also assumes your data do not extend past row 999; if they do, then increase the 999's in the formula to what is appropriate.

TextBox1 is for the ID. TextBox2 is for the Name.

Note that this code is placed in the AfterUpdate event procedure. This is different than your sample code. You used the Change event procedure. The difference is that Change fires on each keystroke while AfterUpdate fires only after the full text is confirmed for the textbox.

Note that you should still add error checking for the case where the ID is not numeric and also for the case where the numeric ID does not match. The code above is simply for demonstrating the technique to display the looked-up value. If you wish for me to flesh it out more, please let me know.

UPDATE

I went ahead and fleshed it out with the error checking:

Private Sub TextBox1_AfterUpdate()
    GetData
End Sub

Public Sub GetData()
    Dim v, w
    On Error Resume Next
    v = Evaluate("=INDEX(B2:B999,MATCH(MAX((IF(A2:A999=" & TextBox1 & _
                 ",1)*(C2:C999)),1),IF(A2:A999=" & TextBox1 & _
                 ",1)*(C2:C999),))")
    w = Evaluate("MAX((IF(A2:A999=" & TextBox1 & ",1)*(C2:C999)))")
    If IsArray(v) Or IsError(v) Then v = "ID not found.": w = ""
    TextBox2 = v
    TextBox3 = "": TextBox3 = CDate(w)
End Sub

UPDATE 2 In the fleshed out version directly above, I added support for the associated date in TextBox3.

网友答案:

You could read the whole range in when the userform opens, sort it, then find the first ID.

Private mvaData As Variant

Private Sub TextBox1_AfterUpdate()
    Me.TextBox2.Text = vbNullString
    Me.TextBox3.Text = vbNullString
    GetData
End Sub

Public Sub GetData()

    Dim i As Long

    For i = LBound(mvaData, 1) To UBound(mvaData, 1)
        If mvaData(i, 1) = Val(Me.TextBox1.Text) Then
            Me.TextBox2.Text = mvaData(i, 2)
            Me.TextBox3.Text = mvaData(i, 3)
            Exit For 'stop after the first one - largest date
        End If
    Next i

End Sub

Private Sub UserForm_Initialize()

    Dim i As Long, j As Long
    Dim lId As Long, sDesc As String, dtDate As Date

    'store the data in a variable when the forms opens
    mvaData = Sheet1.Range("A1:C5")

    'sort with larger dates on top
    For i = LBound(mvaData, 1) To UBound(mvaData, 1) - 1
        For j = i To UBound(mvaData, 1)
            If mvaData(i, 3) < mvaData(j, 3) Then
                lId = mvaData(j, 1)
                sDesc = mvaData(j, 2)
                dtDate = mvaData(j, 3)

                mvaData(j, 1) = mvaData(i, 1)
                mvaData(j, 2) = mvaData(i, 2)
                mvaData(j, 3) = mvaData(i, 3)

                mvaData(i, 1) = lId
                mvaData(i, 2) = sDesc
                mvaData(i, 3) = dtDate
            End If
        Next j
    Next i

End Sub
分享给朋友:
您可能感兴趣的文章:
随机阅读: