CODEWITHSUNDEEP
excelvbaexcel-formula
cena
cena

Reputation: 420

how to select the corresponding values for the row that matches with one of the values in a column?

After saving my combobox values into the worksheet, i am trying to link my combobox to the worksheet. For instance, when the user selects one of the combobox value eg device a that is stored in the worksheet,how to select the corresponding values for the row that matches with one of the values in column A and link them with other comboboxes? Below is my code.

enter image description here

Private Sub selectitems()
With ComboBox1
Activate.Worksheets ("Newdevice")
Dim Y As Long
Y = Worksheets("Newdevice").Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To Y
If Worksheets("Newdevice").Cells(i, 1).Value = ComboBox1.Value Then
'if any one of the value in combobox1 is = to columnA of worksheet("newdevice")
'stuck here
'select the columns that has the same row as the one selected in columnA
ComboBox1.Value=
ComboBox2.Value=
ComboBox3.Value=
ComboBox4.Value=
End If
Next i
End Sub

Upvotes: 0

Views: 382

Answers (2)

Harun24hr
Harun24hr

Reputation: 36870

Use Find method to find value in Column A for ComboBox1 then pull data to other ComboBoxes by Offset(). Use below codes.

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim LEO As Range

Set ws = Worksheets("Sheet1")
With ws.Columns("A:A")
    Set LEO = .Find(what:=Me.ComboBox1, after:=.Cells(1), LookIn:=xlValues, _
        searchorder:=xlByRows, searchdirection:=xlNext)
    If Not LEO Is Nothing Then
        Me.ComboBox2 = LEO.Offset(0, 1)
        Me.ComboBox3 = LEO.Offset(0, 2)
        Me.ComboBox4 = LEO.Offset(0, 3)
    End If
End With

End Sub

enter image description here

#EDIT

To add values to ComboBox from sheet range use following code.

Private Sub UserForm_Initialize()
    With ComboBox1
        .List = Sheets("Sheet1").Range("A1:A8").Value
    End With
End Sub

Upvotes: 1

Variatus
Variatus

Reputation: 14383

Please try this code in place of the one you have. It calls a custom function that looks for the row number, then retrieves cell values from the row the function found. Install this code in a standard code module (by default it's name will be Module1. Give it a meaningful name.)

Option Explicit

Sub PopulateMyForm(MyForm As MSForms.UserForm)

    Dim Rng As Range
    Dim Device As String
    Dim C As Long
    Dim R As Long

    Device = MyForm.ComboBox1.Value
    Set Rng = DeviceRange
    R = FindRow(Device, Rng)
    If R Then
        For C = 2 To 4
            MyForm.Controls("ComboBox" & C).Value = Rng.Worksheet.Cells(R, C).Value
        Next C
    Else
        MsgBox """" & Device & """ wasn't found.", _
               vbInformation, "Unlisted device"
    End If
End Sub

Function DeviceRange() As Range

    Dim Ws As Worksheet
    Dim Rng As Range

    Set Ws = ThisWorkbook.Worksheets("NewDevice")
    With Ws
        ' lookup range in column A, starting from row 2
        Set DeviceRange = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))
    End With

End Function

Private Function FindRow(FndWhat As Variant, _
                         FndIn As Range, _
                         Optional FndAft As Long = 1, _
                         Optional FndVal As Boolean, _
                         Optional FndPart As Boolean, _
                         Optional FndHow As Long = xlByColumns, _
                         Optional FndWay As Long = xlNext, _
                         Optional FndCase As Boolean = False, _
                         Optional Fnd As Range) As Long

    ' returns a sheet row if FndIn is a named range or DataBodyRange

    ' ==================================================
    '   Parameters:
    '       FndWhat         = Text/Number to search for
    '       FndIn           = Range to search in
    '       FndAft          = Cell of FndIn to start the search from
    '       FndVal          = Cell property to search
    '                         [True = Value, False = Formula]
    '       FndPart         = True = xlPart, False = xlWhole]
    '       FndHow          = Search method
    '                         [= xlByRows or xlByColumns]
    '       FndWay          = Search direction
    '                         [= xlNext or XlPrevious]
    '       FndCase         = Match case
    '       Fnd             = Return range
    ' ==================================================


    If FndAft = 0 Then FndAft = FndIn.Cells.Count
    With FndIn
        Set Fnd = .Find(What:=FndWhat, _
                        After:=.Cells(FndAft), _
                        LookIn:=IIf(FndVal, xlValues, xlFormulas), _
                        LookAt:=IIf(FndPart, xlPart, xlWhole), _
                        SearchOrder:=FndHow, _
                        SearchDirection:=FndWay, _
                        MatchCase:=FndCase)
    End With
    On Error Resume Next                      ' return 0, if not found
    FindRow = Fnd.Row
    Err.Clear
End Function

The FindRow function is perhaps more elaborate that you expect. It's a tool I keep handy and use whenever I need to find a row.

I don't know how you link this code to your form. Here is a suggestion. Install the code below in the code module of your userform.

Option Explicit

Private Sub UserForm_Initialize()

    Dim Rng As Range

    With ComboBox1
        .List = DeviceRange.Value
        .ListIndex = 0
    End With
End Sub

Private Sub ComboBox1_Change()

    PopulateMyForm Me
End Sub

The first procedure will run when the form is initialized. It will load the Device names from the DeviceRange. The second procedure will run when an item is selected from the drop-down list in ComboBox1.

Upvotes: 2

Related Questions