如何使用从用户窗体中提取的信息来分析行

Question

我正在尝试创建一个userform，它从userform1获取输入，并将其传递给userform2，后者在userform2上显示属于该数据集的信息。问题是，一旦从第1行(CATBOX)中选择了整个类别，我需要将解析器限制到第2行中的子组件，因为在同一行中还有其他类别中的其他子组件。第1行中的每个类别都是一系列合并的单元格。

我已经尝试过使用' find‘来查找userform1!CATBOX的值，并返回位置来获取起始列。然后我试着找到合并的单元格的范围，这样我就可以得到终点。然后，我尝试将解析器限制在第2行的列范围内，以收集我的信息。我将最后一段代码简单地显示在userform2上，这对我的代码来说并不是必需的。

With ActiveSheet

    Set ra = ActiveSheet.Cells.Find(What:=UserForm1!CATBOX.Value, After:=Range("A1"), _
            LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Set rng = Range(ra)

    If rng.MergeCells Then

        Set rng = rng.MergeArea
        Set rngStart = rng.Cells(1, 1)
        Set rngEnd = rng.Cells(rng.Rows.Count, rng.Columns.Count)
    End If


    Set rag = UserForm2.Controls.Add("Forms.Label.1", "rag", True)
    With rag
        .Caption = rngStart.Address
        .Left = 10
        .Width = 50
        .Top = 50
    End With

    Set rag2 = UserForm2.Controls.Add("Forms.Label.1", "rag2", True)
    With rag2
        .Caption = rngEnd.Address
        .Left = 70
        .Width = 50
        .Top = 50
    End With

    End With

我希望得到的结果是能够解析第二行信息，限制在它上面的合并类别所建立的列的范围内。

Answer 1

欢迎使用SO.Though，需求和工作表数据布局不清楚，假设如下。

所使用的代码可以根据您的需求进行修改，并且可以从CATBOX的更改事件移动到任何合适的事件

Private Sub CATBOX_Change()
Dim Rng As Range, SubRng As Range
Dim Rw As Long, ColSt As Long, ColEnd As Long, i As Long, ScatNo As Long
Dim Rag As Object

With ThisWorkbook.ActiveSheet

Set Rng = .Rows(1).Find(What:=UserForm1.CATBOX.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If Rng Is Nothing Then Exit Sub

Set Rng = Rng.MergeArea
Set rngstart = Rng.Cells(1, 1)
Set rngEnd = Rng.Cells(Rng.Rows.Count, Rng.Columns.Count)
Rw = Rng.Row + Rng.Rows.Count
ColSt = Rng.Column
ColEnd = Rng.Column + Rng.Columns.Count - 1
Debug.Print Rw, ColSt, ColEnd

Set Rng = .Range(.Cells(Rw, ColSt), .Cells(Rw, ColEnd))
ScatNo = 0
   For Each SubRng In Rng
        If SubRng.Value <> "" Then
        ScatNo = ScatNo + 1
        Set Rag = UserForm2.Controls.Add("Forms.Label.1", "Scat" & ScatNo)
        Rag.Caption = SubRng.Value
        Rag.Left = 70
        Rag.Width = 50
        Rag.Top = ScatNo * 30
        End If
   Next
End With
UserForm2.Show
End Sub