第一篇文章。我下面有相对简单的代码,并且正在



错误。我知道将独立代码放入一个Sub时可以使用,但是由于各种原因,我想将其拆分出来,以便在更大的工作簿中,我可以仅调用第二个Sub,而不必复制并粘贴多个循环次。该代码的目的是在excel中自动调整指定范围的大小。

Sub letsGo()
Dim rng As Range
Dim sht As Worksheet
Set rng = ThisWorkbook.Sheets("Sheet1").Range("Range1")
Set sht = ThisWorkbook.Sheets("Sheet1")
Call whyDoesntThisWork(sht, rng)
End Sub

Private Sub whyDoesntThisWork(rangeSheet As Sheet, rangeTable As Range)
Dim Col As Range
Dim reSize As Range
For Each Col In rangeTable.Columns
    If Col.Hidden = False Then
        Set reSize = rangeSheet.Range(rangeSheet.Cells(rangeTable.Row, Col.Column), rangeSheet.Cells(rangeTable.Rows.Count, Col.Column)) reSize.Columns.autoFit
    End If
Next Col
End Sub

最佳答案

您有两种不同的数据类型:

Private Sub whyDoesntThisWork(rangeSheet As Sheet, rangeTable As Range)

rangeSheet是一个工作表,但是当您调用它时,您将传递:
Call whyDoesntThisWork(sht, rng)

sht is of type WorkSheet

那是你的前后矛盾。我建议您将定义更改为:
Private Sub whyDoesntThisWork(rangeSheet As WorkSheet, rangeTable As Range)

10-07 19:34
查看更多