VBA-使用当前选择作为范围对象 [英] VBA - Using Current Selection As Range Object

问题描述

我在下面具有此功能，该功能执行以下操作:

I have this function below which does the following:

1. 采用两个参数(标题名称，所需的功能).
2. Header Name参数用于查找标题，并随后标识该列直到最后一行的范围.
3. 所需的功能"参数用于在select语句中切换所需的任何其他步骤.
4. 在大多数语句的末尾，我执行 Range.Select ，然后以选定的范围退出函数.

1. Takes two parameters (Header Name, Function Needed).
2. The Header Name parameter is used to find the heading and subsequently to identify the range of that column up until the last row.
3. The Function Needed parameter is used to switch in the select statement for any additional steps needed.
4. At the end of most of the statements, I do a Range.Select then I exit my function with a selected range.

这是代码:

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

当我尝试清理代码时，遇到了一个专门针对硬编码范围的部分，而我试图使用我的函数，但是，现在我无法使用正确使用此功能，因为我无法将范围传递"给子对象，而且似乎无法使选择成为子对象所需的范围对象.

As I am trying to clean up my code, I have come across a section where I have specifically hard coded ranges and I am trying to make use of my function instead, however, I am now at a point where I am unable to make use of this function correctly as I cannot "pass" the range back to the sub and I cannot seem to make the selection the range object needed for the 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

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

Before I used the function, the code looked like this:

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

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

现在这2行是在函数中执行的，因此我在子例程中不需要它.我尝试了以下方法:

Now these 2 lines are performed in the function, so I don't need it in the sub. I have tried the following:

Set rngMyRange = Selection

Set rngMyRange = ActiveSheet.Range(Selection.Address)

Set xRg = ws2.Range(rngMyRange  & i)

但是我得到了错误:

类型不匹配

所以我在想:

1. 在函数中选择范围，然后在子菜单中使用它-但是如何?
2. 弄清楚如何将实际范围对象从函数传递给子对象

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

Although the second option would require some extra changes in my code, I would think this is the better option to go with.

推荐答案

好，下面是一个插图，您可以了解我的意思.如果在B2:J2中的某个位置放置一个"，它将选择范围.我仅在此处使用选择"，以便您可以看到其标识的范围.(免责声明:我不完全了解您在做什么，并且不确定您是否需要所有这些代码来实现所需的功能.)

Ok, so here is an illustration just so you can see what I mean. If you put "one" somewhere in B2:J2 it will select the range. I am only using Select here so that you can see the range it identifies. (Disclaimer: I don't fully understand what you are doing, and not sure you need all this code to achieve what you want.)

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

The Function now returns a range variable, and is assigned to r. Run the procedure 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

这篇关于VBA-使用当前选择作为范围对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！