views:

27

answers:

1

Hi! I have a form with a couple of combo boxes. The first combobox adds items based on each row in an excel sheet.

Public Sub FetchExcelValues(ByVal ControlType As String, ByVal control As Object, ByVal xlApp As Object, ByVal xlWorkBook As Object, ByVal xlWorkSheet As Object, ByVal column As String, ByVal row As Integer)

    Dim iTeller = row
    Dim tekst As String

    Do
        tekst = xlWorkSheet.Cells(iTeller, column).Value
        If tekst <> "" Then
            If ControlType = "Tekstboks" Then
                control.text = tekst
            End If
            If ControlType = "Combobox" Then
                control.Items.Add(tekst)
            End If
            If ControlType = "Label" Then
                control.text = tekst
            End If
        End If
        iTeller = iTeller + 1
    Loop Until tekst = ""

End Sub

This basically reads each cell in the specified column until it hits a blank cell. Then it takes the value of each cell and adds it as items to the first combo box. Now, what I want the code to do next is to take the selected item from this combo box and look for a match in another worksheet. When it finds a cell that matches, it should pick up the value from column B of the same row as the match. There might be more than one match as well, so every time it finds a match, I want to pick up the value of Column B of the same row and add THAT as items in the second combobox.

What's the best way of achieving this?

+2  A: 

Sounds like you might just need to make use of the VLookup or HLookup functions in Excel. they work similar to what you're looking to do with no coding, just formulas.

drventure
Ok, so I can actually use Excel functions within vb.net. That's really cool, I think that sounds more logical :)
Kenny Bones
Well, that's not exactly what I meant, but yes you can use Excel functions from VB.net, you'd just have to write the formula into a cell, recalc, and read the value of the cell. But if you could most of this via just excel formulas and lookup tables, that might be the simpler approach. There's not really enough background on what kind of app you're build to know which would be best.
drventure