VBA - USERFORM - Find equall value and make entry

2019-09-15 16:43发布

I need upgrade this code to find.next version. In attachment is sample form for better understanding. Keycombobox value can be found more then once and every match for adjacent values has to be in adjacent textbox.

DATA SEMPLE Keytextbox value = TEST1

.Cells(row with FIRST find TEST1, 1) = textbox10 (located in multipage.page(find))
.Cells(row with SECOND find TEST1, 1) = textbox110 (locateted in multipage.page(alternative find))

enter image description here

Option Explicit

    Sub TestFind()

    Dim sonsat As Long
    Dim FindRng As Range

    With Sheets("DATA")
        Set FindRng = .Range("A:A").Find(Keycombobox.Text) ' <-- assuming  Keycombobox is a textBox

        If Not FindRng Is Nothing Then ' <-- successful find
            sonsat = FindRng.Row

            ' rest of yout code here ....
            .Cells(sonsat, 1) = TextBox10 '<-- for good coding practice use TextBox1.Value ' or TextBox1.Text
            .Cells(sonsat, 2) = TextBox20
            .Cells(sonsat, 3) = TextBox30
            .Cells(sonsat, 4) = TextBox40
            .Cells(sonsat, 5) = TextBox50
            .Cells(sonsat, 6) = TextBox60
            .Cells(sonsat, 7) = TextBox70
        Else
            MsgBox "Unable to find " & Keycombobox.Text & " in specified Range !"
        End If
    End With

    End Sub

1条回答
戒情不戒烟
2楼-- · 2019-09-15 17:30

may be you're after this:

Sub TestFind()
    Dim f As Range
    Dim firstAddress As String
    Dim iPage As Long, i As Long

    With Sheets("DATA").Range("A:A").SpecialCells(xlCellTypeConstants)
        Set f = .Find(what:=Keycombobox.Text, LookIn:=xlvalkue, lookat:=xlWhole) ' <-- assuming  Keycombobox is a textBox
        If Not f Is Nothing Then
            firstAddress = f.address
            Do
                For i = 1 To 7
                    Me.Controls("TextBox" & iPage + i * 10) = .Cells(f.Row, i)
                Next
                iPage = iPage + 100
                Set f = .FindNext(f)
            Loop While f.address <> firstAddress
        End If
    End With
End Sub
查看更多
登录 后发表回答