在VBA代码中，如何通过定义范围End（XlDown）为每个单元格方法向列表框中添加值？ [英] In VBA code,how to add values to a listbox using for each cell approach with defining a range End(XlDown)?

问题描述

我已经创建了一个带有Forms和Word到Excel的宏文件。

I have created a macro file with Forms and Word to Excel.

在此编码中，没有什么符合我的期望。

In this coding fewthings are not working as per my expectation.

1. 从Excel数据库获取唯一的员工姓名。
   我想从excel数据库添加唯一的员工姓名，并将get保存在工作表中。之后，将这些值添加到列表框中。在这里，我无法定义 A1：A10之类的范围。我想从A1中选择数据作为结束数据。

如果

我需要帮助来定义下面给出的范围和代码

I need help in defining the range and code given below

ListEmployeeName.Clear
For Each cell In Worksheets("SunEmployeeDetails").Range("A1").End(xlDown)
    ListEmployeeName.AddItem (cell.Value)
Next
    ListEmployeeName.Value = Worksheets("SunEmployeeDetails").Range("A1")
End Sub

推荐答案

查找最后一行，然后定义您的范围 Range（ A1：A& LastRow）

Find Last Row and then define your range Range("A1:A" & LastRow)

您还可以找到最后一行，并使用 For 循环遍历范围。另外，要获得唯一的员工姓名，您可以将 On Error Resume Next 与 Collection 一起使用，如下所示。我已在下面的代码中添加了注释，因此您在理解它时应该没有问题。

You can also find the last row and loop through the range using a For loop. Also to get unique Employee Name, you can use On Error Resume Next with a Collection as shown below. I have commented the code below so you should not have a problem understanding it. But if you do then simply ask.

这是您要尝试的吗？ （未经测试）。

Is this what you are trying? (Untested).

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long, i As Long
    Dim col As New Collection
    Dim itm As Variant

    Set ws = Worksheets("SunEmployeeDetails")

    With ws
        '~~> Find Last row
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        '~~> Loop through the range and add it to the unique
        '~~> collection using "On Error Resume Next"
        For i = 1 To lRow
            On Error Resume Next
            col.Add .Range("A" & i).Value, CStr(.Range("A" & i).Value)
            On Error GoTo 0
        Next i
    End With

    ListEmployeeName.Clear

    '~~> add the itme from collection to the listbox
    For Each itm In col
        ListEmployeeName.AddItem itm
    Next itm
End Sub

这篇关于在VBA代码中，如何通过定义范围End（XlDown）为每个单元格方法向列表框中添加值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！