运行时错误13 - 如果for循环中的函数类型不匹配（尝试匹配字符串的错误）

I am trying to locate the first cell (row) stating "#N/A" in specific columns. I cannot work around a type mismatch error I get. I have googled and read a lot of similar stackflow questions and answers but still could not solve it.

我试图在特定列中找到第一个表示“#N / A”的单元格(行)。我无法解决我得到的类型不匹配错误。我用谷歌搜索并阅读了很多类似的stackflow问题和答案,但仍然无法解决它。

The main things I have tried so far (besides various little changes):

到目前为止我尝试过的主要内容(除了各种小变化):

used the immediate window and debug print to check outputs (the GetDates sub is working correctly)

使用立即窗口和调试打印来检查输出(GetDates子工作正常)

converting the collection to an array where I can define a data type

将集合转换为可以定义数据类型的数组

using a while function instead of for (in this case I get it to attempt the while function but on the last iteration I get a type mismatch again)

使用while函数而不是for(在这种情况下,我得到它来尝试while函数,但在最后一次迭代中我再次遇到类型不匹配)

here is the full code:

这是完整的代码:

Dim EndofWeekDates As New Collection
Dim EndofRange As New Collection

Dim lCol As Long
Dim lRow As Long
Dim i As Long
Dim j As Long
Dim v As Long
Dim x As Long


Sub GetDates()

    Set EndofWeekDates = Nothing
    i = 4
    j = 1
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column

    While j < lCol + 1
        If Not IsEmpty(Cells(i, j).Value) And Not Cells(i, j).Value = "End" Then
            EndofWeekDates.Add j
        End If
        j = j + 1
    Wend

    Call GetRange

End Sub

Sub GetRange()

    Set EndofRange = Nothing
    For x = EndofWeekDates.Count To 1 Step -1
        lRow = Cells(Rows.Count, EndofWeekDates(x)).End(xlUp).Row

        For v = 15 To lRow
            If Cells(v, EndofWeekDates(x)).Value = "#N/A" Then
                EndofRange.Add v
                Exit For
            End If
        Next v

    Next x

End Sub

I get the error in the following section on the IF line

我在IF行的下一节中得到了错误

For v = 15 To lRow
   If Cells(v, EndofWeekDates(x)).Value = "#N/A" Then
      EndofRange.Add v
      Exit For
   End If
Next v

The EndofWeekDates(x) should be constant during each 15 - lRow run while v changes. I have tried putting in the variable i (used earlier) instead of v and it works but only if i remains constant and is not changed in the for loop. As far as I can see the issue is with the v and not with EndofWeekDates(x). Furthermore, it seems that the issue only occurs when I do not use a constant but a changing number per for iteration. I tried to use the same while function as in GetDates but that did not solve it either.

在每次15-lRow运行期间,当v改变时,EndofWeekDates(x)应该是恒定的。我已经尝试输入变量i(之前使用过)而不是v并且它可以工作但只有当我保持不变并且在for循环中没有改变时。据我所知,问题在于v而不是EndofWeekDates(x)。此外,似乎问题只发生在我不使用常量而是每次迭代更改数字时。我试图在GetDates中使用相同的函数,但是也没有解决它。

Since v is declared as Long and I have also tried integer, I am stuck. Especially since the earlier used Cells.Value works with a Long which is increased in each iteration.

因为v被声明为Long而我也尝试了整数,所以我被卡住了。特别是因为早期使用的Cells.Value与Long一起工作,在每次迭代中都会增加。

3 个解决方案

#1

Your line

If Cells(v, EndofWeekDates(x)).Value = "#N/A" Then

is crashing because the cell does not contain the string "#N/A" but instead contains an error code, which Excel displays as #N/A.

崩溃是因为单元格不包含字符串“#N / A”,而是包含错误代码,Excel显示为#N / A.

A comparison of the error code to a string cannot be performed as there is no type conversion that allows the two sides of the comparison to be cast to a common data type - therefore it generates a "type mismatch" error.

无法执行错误代码与字符串的比较,因为没有允许比较的双方转换为公共数据类型的类型转换 - 因此它会生成“类型不匹配”错误。

The correct way to test for an #N/A error condition would be

测试#N / A错误条件的正确方法是

If Application.IsNA(Cells(v, EndofWeekDates(x))) Then

#2

This is a very peculiar error. I am not sure what is causing it but using Cells().Text instead of Cells().Value will work properly.

这是一个非常特殊的错误。我不确定是什么导致它,但使用Cells()。文本而不是Cells()。值将正常工作。

#3

Try below

    For v = 15 To lRow
       If Cells(v, EndofWeekDates(x)).Text = "#N/A" Then
          EndofRange.Add v
          Exit For
       End If
    Next v

Alternatively,

For v = 15 To lRow
   If Application.WorksheetFunction.IsNA(Cells(v, EndofWeekDates(x))) Then
      EndofRange.Add v
      Exit For
   End If
Next v

#1