过程有效，但当变成一个要调用的Public Sub时，它会失败 [英] Procedure works but when turned into a Public Sub to be called it then fails

问题描述

我写了以下子：

 Public Sub filterEmployeeSheets（XLSheets As Excel.Worksheet，SearchRange As String，Indicator as String，FilterString As String）
 
'此Sub用于通过删除任何不包含变量filterString中所述值的行
'来过滤工作表
 
'@Parameter Sheets声明工作表名称
'@Parameter SearchRange将列设置为过滤
'@Parameter指标确定=，<>设置
'@Parameter FilterString设置字符串以保持
 
 Dim lngLr As Long 
 
使用XLSheets 
 
 lngLr = .Cells。 Find（What：=*，SearchDirection：= Excel.XlSearchDirection.xlPrevious，SearchOrder：= Excel.XlSearchOrder.xlByRows）。
 
如果lngLr> 1然后
使用.Range（SearchRange& lngLr）
 **。自动过滤（字段：= 1，标准1：=指示符和过滤字符串）**'错误在这里
 .EntireRow。删除（）
结束
结束如果
结束
结束子
 
公共函数ClientSheets（Index As Long）作为Excel.Worksheet 
 
'这个函数索引在安装过程中在各种循环中使用的所有Employee表
'
'@ param EmployeeSheets，是索引
 
的表单选择案例索引
 
案例1：返回xlWSAllEEAnnul 
案例2：返回xlWSAllEEHourly 
结束选择
 
抛出新的ArgumentOutOfRangeException（Index）
 
结束功能

当我按照以下程序致电时：

 Dim xlRefSheets As Excel.Worksheet 
 
 For i Long = 1 to 2 Step 1 
 
昏暗strOperatorSymbol As String =<> 
 
 xlRefSheets = ClientSheets（i）
 
 filterEmployeeSheets（xlRefSheets，K5：K，<>，Y）
 
下一个
 
结束子

我收到此错误：使用范围无法完成命令指定。选择范围内的单个单元格并再次尝试该命令。但是，如果我在一张纸上使用Public Sub作为没有For循环的程序而不是调用它，它就可以正常工作。

解决方案

我想你是这里没有正确地通过范围 -



filterEmployeeSheets（xlRefSheets， K5：K ，<>，Y ）

I wrote the following sub:

Public Sub filterEmployeeSheets(XLSheets As Excel.Worksheet, SearchRange As String, Indicator As String, FilterString As String)

    'This Sub is used to filter sheets by deleting any rows
    'that do not contain the value stated in variable filterString

    '@Parameter Sheets to declare sheet(s) name
    '@Parameter SearchRange to set the column to filter
    '@Parameter Indicator determines the =, <> setting
    '@Parameter FilterString to set the string to keep

    Dim lngLr As Long

    With XLSheets

        lngLr = .Cells.Find(What:="*", SearchDirection:=Excel.XlSearchDirection.xlPrevious, SearchOrder:=Excel.XlSearchOrder.xlByRows).Row

        If lngLr > 1 Then
            With .Range(SearchRange & lngLr)
                **.AutoFilter(Field:=1, Criteria1:=Indicator & FilterString)** 'Error is here
                .EntireRow.Delete()
            End With
        End If
    End With
End Sub

Public Function ClientSheets(Index As Long) As Excel.Worksheet

    'This function indexes all of the Employee sheets
    'to use in various loops during he instal process
    '@param EmployeeSheets, are the sheets to index

    Select Case Index

        Case 1 : Return xlWSAllEEAnnul
        Case 2 : Return xlWSAllEEHourly
    End Select

    Throw New ArgumentOutOfRangeException("Index")

End Function

When I call it on the procedure below:

Dim xlRefSheets As Excel.Worksheet

        For i As Long = 1 To 2 Step 1

            Dim strOperatorSymbol As String = "<>"

            xlRefSheets = ClientSheets(i)

            filterEmployeeSheets(xlRefSheets, "K5:K", "<>", "Y")

        Next

End Sub

I get this error: The command could not be completed by using the range specified. Select a single cell within the range and try the command again. However, If I use the Public Sub as a procedure without the For Loop on a single sheet instead of calling it, it works just fine.

解决方案

I think you are not passing the range properly here -

filterEmployeeSheets(xlRefSheets, "K5:K", "<>", "Y")

这篇关于过程有效，但当变成一个要调用的Public Sub时，它会失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！