代码之家 › 专栏 › 技术社区 › smrmodel78

VBA创建表格未选择正确的范围

vba excel

smrmodel78 · 技术社区 · 2 年前

我找到以下代码来获取TrialBalance工作表上的数据,并将其转换为vba中的表。它可以创建表并对其进行重命名,但范围需要从A2开始,因为A2是存储表标题的位置。

Sub ConvertTrialBalanceToTable()
Dim wb1 As Workbook

Set wb1 = ActiveWorkbook 'Trial Balance Template File

wb1.Sheets("TrialBalance").Range("A2").CurrentRegion.Select
        If ActiveSheet.ListObjects.Count < 1 Then
            ActiveSheet.ListObjects.Add.Name = ActiveSheet.Name
        End If

End Sub

0 回复 | 直到 2 年前

VBasic2008 2 年前

转换' `CurrentRegion` '占据第一个单元格上方的行或左侧的列时,返回Excel表格

如果代码位于 TrialBalance模板文件 使用 ThisWorkbook 而不是 ActiveWorkbook 。

Sub ConvertTrialBalanceToTable()
    
    With ActiveWorkbook.Worksheets("TrialBalance")
        If .ListObjects.Count = 0 Then
            .ListObjects.Add(xlSrcRange, _
                RefCurrentRegion(.Range("A2")), , xlYes).Name = .Name
        End If
    End With

End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns a reference to the range starting with the first cell
'               of a range and ending with the last cell of the first cell's
'               Current Region.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function RefCurrentRegion( _
    ByVal FirstCell As Range) _
As Range
    Const ProcName As String = "RefCurrentRegion"
    On Error GoTo ClearError

    If FirstCell Is Nothing Then Exit Function
    With FirstCell.Cells(1).CurrentRegion
        Set RefCurrentRegion = FirstCell.Resize(.Row + .Rows.Count _
            - FirstCell.Row, .Column + .Columns.Count - FirstCell.Column)
    End With

ProcExit:
    Exit Function
ClearError:
    Debug.Print "'" & ProcName & "' Run-time error '" _
        & Err.Number & "':" & vbLf & "    " & Err.Description
    Resume ProcExit
End Function

推荐文章