CODEWITHSUNDEEP
vbaexcel
Nataniell
Nataniell

Reputation: 71

VBA - USERFORM - Find equall value and make entry for adjacent value in row to textbox for every match

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

Upvotes: 0

Views: 775

Answers (1)

user3598756
user3598756

Reputation: 29421

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

Upvotes: 1

Related Questions