Excel vba：基于列A中的单元格获取列B上的单元格范围 [英] Excel vba: Getting range of cells on column B based on cell in column A

问题描述

我有一个vba脚本，从所有工作表获取信息，将它们添加到不同的组合框，每当从组合框中选择一个值时，会发生不同的事情。

第一个组合我填充如下：

I have a vba script that gets info from all sheets, adds them to different comboboxes and whenever a value is selected from a combobox different things happens.
The first combo I populate as following:

Private Sub Workbook_Open()

Dim oSheet As Excel.Worksheet
Dim oCmbBox As MSForms.ComboBox
Set oCmbBox = ActiveWorkbook.Sheets(1).cmbSheet
oCmbBox.Clear
For Each oSheet In ActiveWorkbook.Sheets
    If oSheet.Index > 1 Then
        oCmbBox.AddItem oSheet.Name
    End If
Next oSheet

End Sub

第二个组合框执行以下操作：

The second combobox is doing the following:

Private Sub cmbSheet_Change()

Dim oSheet As Excel.Worksheet

'Report combo box
Dim oCmbBox As MSForms.ComboBox
Set oCmbBox = ActiveWorkbook.Sheets(1).cmbSheet

'Tech combo box
Dim tCmbBox As MSForms.ComboBox
Set tCmbBox = ActiveWorkbook.Sheets(1).techCombo
tCmbBox.Clear

Dim rng As Range
Set rng = Sheets(oCmbBox.Value).Range("A2:A10")
For Each cell In rng
    If Not IsEmpty(cell.Value) Then
        tCmbBox.AddItem cell.Value
    End If
Next cell

ActiveWorkbook.Sheets(1).techCombo.ListIndex = 0
End Sub

我有一张表格：

I have a following sheet:

现在，从techCombo（即Teknik_1）中选择一个值我想要使用从B6到B9的数据填充第三个组合框。

那么可能??

提前感谢！

Now, selecting a value from techCombo (ie Teknik_1) I want the third combobox to be populated with data ranging from B6 - B9.
Is that possible??
Thanks in advance!

推荐答案

给定你提供的示例数据，为你工作。请注意，此代码在Sheet1代码模块中：

Given your provided example data, something like this should work for you. Note that this code is in the Sheet1 code module:

Private Sub techCombo_Change()

    Dim ws As Worksheet
    Dim rFound As Range
    Dim cbo3 As ComboBox

    Set cbo3 = Me.ComboBox3 'Change to the actual name of the third combobox

    cbo3.Clear

    With Me.cmbSheet
        If .ListIndex = -1 Then Exit Sub    'Nothing selectd in cmbSheet
        Set ws = ActiveWorkbook.Sheets(.Text)
    End With

    With Me.techCombo
        If .ListIndex = -1 Then Exit Sub    'Nothing selected in techCombo
        Set rFound = ws.Columns("A").Find(.Text, ws.Cells(ws.Rows.Count, "A"), xlValues, xlWhole)
    End With

    If Not rFound Is Nothing Then
        If Trim(Len(rFound.Offset(2, 1).Text)) = 0 Then
            cbo3.AddItem rFound.Offset(1, 1).Value
        Else
            cbo3.List = ws.Range(rFound.Offset(1, 1), rFound.Offset(1, 1).End(xlDown)).Value
        End If
    End If

End Sub

这篇关于Excel vba：基于列A中的单元格获取列B上的单元格范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！