时间戳

首页 > 解决方案 > 浏览它们时如何使用获取列数据？

excel - 浏览它们时如何使用获取列数据？

问题描述

我有多张纸，每张只有 1 张不同宽度和高度的桌子。

我正在尝试实现：一旦用户通过组合框选择了工作表（这有效），我就可以在该工作表上列出表中的标题。

我可能的解决方案想法：我的想法是在用户表单的组合框中列出表头。

  1. 我计算所选工作表上的列，工作
  2. for 循环遍历列以从每个列中获取标题名称并堆叠到 combobox.list 中，

代码：

Private Sub chcSite_Change()
Dim siteSheet As String
siteSheet = WorksheetFunction.VLookup(Me.chcSite.Value, Worksheets("Overview").Range("SiteTable"), 2, False)

Me.chcRange.Enabled = True  ' enables combobox for headers list

Dim COLS As Integer
COLS = Worksheets(siteSheet).ListObjects(1).ListColumns.Count
Dim i As Integer
i = 1

For i = 1 To COLS   
    If Worksheets(siteSheet).Cells(Columns(i), 1) = "" Then Exit For   ' if header is empty = also end of table cols.
    MsgBox Worksheets(siteSheet).Cells(Columns(i), 1)  ' debug to see what it returns.

Next i


'Me.chcRange.List = Worksheets(siteSheet).ListObjects(1).ColumnHeads ' random test of columnheads

End Sub

如您所见，我期望 Worksheets(siteSheet).Cells(Columns(i), 1) 返回一些东西，但它似乎只是一个指针/选择器。

标签： excelvba

解决方案

You might benefit from reading The VBA Guide To ListObject Excel Tables.

For example to get the 3rd heading of a table use 

.ListObjects("Table1").HeaderRowRange(3)

The ListObject has its own row/column numbering and may be different from the sheets row/column numbering.

It should look something like this:

Dim i As Long 'always use Long
'i = 1 not needed

For i = 1 To COLS   
    ' v-- this part is not needed …
    If Worksheets(siteSheet).ListObjects(1).HeaderRowRange(i) = "" Then 
        Exit For   ' if header is empty = also end of table cols.
    End If
    ' ^-- … because headers of ListObjects can not be empty by definition.
    '       And HeaderRowRange can only access the headers of the ListObjects.

    MsgBox Worksheets(siteSheet).ListObjects(1).HeaderRowRange(i)  ' debug to see what it returns.
Next i

So you can shorten it to: 

Dim i As Long 
For i = 1 To COLS   
    MsgBox Worksheets(siteSheet).ListObjects(1).HeaderRowRange(i)  ' debug to see what it returns.
Next i

Note that Cells(Columns(i), 1) could not work because eg Columns(2) references the complete column B (it is a range representing the full column 2) and Cells needs a row/column number like Cells(row, column).

推荐阅读