对于循环与LCase抛出异常 [英] For Loop with LCase throwing exception

问题描述

您好：



以下过程应该遍历我的工作表，删除前7行，然后删除名称与下列名称不匹配的所有列。



我的代码的第一部分工作正常，删除前7行。但是，当它到达这一行时：

Hello:

The following procedure is supposed to iterate through my worksheets, delete the first 7 rows and then delete all columns where the names do not match those below.

The first part of my code works fine, it deletes the first 7 rows. However, when it gets to this line:

Select Case LCase(.UsedRange.Cells(1, i))

我收到以下错误：

I get the following error:

System.Reflection.AmbiguousMatchException was unhandled by user code
  Message=Overload resolution failed because no Public 'LCase' can be called with these arguments:
    'Public Shared Function LCase(Value As Char) As Char':
        Argument matching parameter 'Value' cannot convert from '__ComObject' to 'Char'.
    'Public Shared Function LCase(Value As String) As String':
        Argument matching parameter 'Value' cannot convert from '__ComObject' to 'String'.

我无法弄清楚我在这里做错了什么。整个过程如下：

I can't quite figure out what I am doing wrong here. The whole procedure is as follows:

Dim xlWB As Excel.Workbook = CType(Globals.ThisWorkbook.Application.ActiveWorkbook, Excel.Workbook)
Dim xlWSPosition As Excel.Worksheet = CType(CType(xlWB.Sheets("byPosition"), Excel.Worksheet), Excel.Worksheet)
Dim xlWSEmployee As Excel.Worksheet = CType(CType(xlWB.Sheets("byEmployee"), Excel.Worksheet), Excel.Worksheet)

Dim xlSheets As Object
Dim xlSheetsArray(0 To 1) As Excel.Worksheet
Dim k As Long
Dim i As Long

    xlSheetsArray(0) = xlWSPosition
    xlSheetsArray(1) = xlWSEmployee

    For Each xlSheets In xlSheetsArray

        With xlSheets

            'With the above sheets, replace the first 7 rows. They only contain Payscale generic information so we can get rid of it.
            .Rows("1:7").Delete(Excel.XlInsertShiftDirection.xlShiftDown)


            k = .UsedRange.Columns.Count

            'For the referenced sheets above delete all columns except for the ones liste below.  These columns the reference column for the entire workbook.

            For i = k To 1 Step -1
                Select Case LCase(.UsedRange.Cells(1, i))
                    Case "#ees", "annu. base at target", "annualized fte base pay", "annu. base mkt - 10th" 


                        'do nothing
                    Case Else
                        .UsedRange.Columns(i).Delete()
                End Select
            Next i
        End With

    Next xlSheets

End Sub

推荐答案

您正在尝试将对象传递给期望字符串的函数。试试

You are trying to pass an object to a function that is expecting a string. Try

lcase(.usedrange.cells(1, i).value)

或类似。

or similar.

这篇关于对于循环与LCase抛出异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！