excel - VBA - 使用当前选择作为范围对象

Question

我在下面有这个函数，它执行以下操作:

采用两个参数(标题名称，所需功能)。

Header Name 参数用于查找标题并随后标识该列的范围，直到最后一行。

Function Needed 参数用于在 select 语句中切换所需的任何其他步骤。

在大多数语句的末尾，我做了一个 Range.Select然后我以选定的范围退出我的函数。

这是代码:

Function find_Header(header As String, fType As String)
    Dim aCell As Range, rng As Range
    Dim col As Long, lRow As Long
    Dim colName As String

    With ActiveSheet
        Set aCell = .Range("B2:J2").Find(What:=header, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)

        'If Found
        If Not aCell Is Nothing Then
            col = aCell.Column
            colName = Split(.Cells(, col).Address, "$")(1)

            lRow = Range(colName & .Rows.count).End(xlUp).Row + 1

            Set myCol = Range(colName & "2")

            Select Case fType
                Case "Copy"
                    'This is your range
                    Set rng = Range(myCol.Address & ":" & colName & lRow).Offset(1, 0)

                    rng.Select
            End Select

        'If not found
        Else
            MsgBox "Column Not Found"
        End If
    End With

End Function

当我试图清理我的代码时，我遇到了一个我有专门硬编码范围的部分，我正在尝试使用我的函数，但是，我现在处于无法使用的地步正确使用此函数，因为我无法将范围“传递”回 sub，而且我似乎无法选择 sub 所需的范围对象。

这是子中的内容:

Sub Copy_Failed()
    Dim xRg As Range, xCell As Range
    Dim i As Long, J As Long, count As Long
    Dim fType As String, colName As String
    Dim y As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet

    myarray = Array("Defect", "System", "Script")
    myEnv = Array("SIT", "UAT")
    myDefects = Array("New", "Existing")

    i = Worksheets("Run Results").UsedRange.Rows.count
    J = Worksheets("Failed").UsedRange.Rows.count

    Set y = Workbooks("Template.xlsm")

    Set ws1 = y.Sheets("Failed")
    Set ws2 = y.Sheets("Run Results")

    count = 3

    If J = 1 Then

        If Application.WorksheetFunction.CountA(ws1.UsedRange) = 0 Then J = 0

    End If

    ws2.Activate

    fType = "Copy"
    colName = "Status"

    Call find_Header(colName, fType)
End Sub

在我使用该函数之前，代码如下所示:

lngLastRow = Cells(Rows.count, "B").End(xlUp).Row

Set xRg = ws2.Range("E3:E" & lngLastRow & i)

现在这 2 行是在函数中执行的，所以我在 sub 中不需要它。我尝试了以下方法:

Set rngMyRange = Selection

Set rngMyRange = ActiveSheet.Range(Selection.Address)

Set xRg = ws2.Range(rngMyRange  & i)

但我得到了错误:

Type mismatch

所以我在想:

选择函数中的范围，然后在子中使用它 - 但是如何？

弄清楚如何将实际范围对象从我的函数传递到子

尽管第二个选项需要对我的代码进行一些额外的更改，但我认为这是更好的选择。

Answer 1

好的，所以这里有一个插图，你可以明白我的意思。如果您在 B2:J2 某处放置“一个”，它将选择范围。我在这里只使用 Select 以便您可以看到它标识的范围。 (免责声明:我不完全理解你在做什么，并且不确定你需要所有这些代码来实现你想要的。)

该函数现在返回一个范围变量，并分配给 r .运行程序x .

Sub x()

Dim r As Range

Set r = Range("a1", find_Header("one", "Copy"))
r.Select

End Sub

Function find_Header(header As String, fType As String) As Range

Dim aCell As Range, rng As Range
Dim col As Long, lRow As Long
Dim colName As String

With ActiveSheet
    Set aCell = .Range("B2:J2").Find(What:=header, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    'If Found
    If Not aCell Is Nothing Then
        col = aCell.Column
        colName = Split(.Cells(, col).Address, "$")(1)
        lRow = Range(colName & .Rows.Count).End(xlUp).Row + 1
        Set myCol = Range(colName & "2")
        Select Case fType
            Case "Copy"
                'This is your range
                Set find_Header = Range(myCol.Address & ":" & colName & lRow).Offset(1, 0)
            End Select
    'If not found
    Else
        Set find_Header = Nothing
    End If

End With

End Function