Excel VBA：如何解决Index和Match函数类型不匹配的错误 [英] Excel VBA: how to solve Index and Match function type mismatch error

问题描述

当日期计数器更改时，我遇到索引和匹配函数中的错误。当我面对错误时，我写了一个评论。如果需要，我上传了我的数据样本。
示例： http://s000.tinyupload.com/?file_id=00243748825638974221

I encounter error in index and match functions when the counter of date changes. I wrote a comment when I face with error. I uploaded a sample of my data if it needed. sample : http://s000.tinyupload.com/?file_id=00243748825638974221

这里是代码：

Sub regionalAverage()

Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

' *** change the declaration here ***
Dim aname() As String
Dim rw As Variant
Dim col As Variant
Dim date_ini As Date
Dim date_fin As Date

'create WorkSheet

' *** add Redim here, so the index of the array will start from 1 ***
ReDim aname(1 To 2)

date_ini = #1/1/2008#
date_fin = #1/2/2008#
For j = 1 To 3
    For conteo = date_ini To date_fin
        For i = 1 To 2
            With Sheets(i)
               With Application

                    col = .Match(j, Worksheets(i).Range("F2:F23393"), 0)
                    rw = .Match(CLng(conteo),     Worksheets(i).Range("D2:D23393"), 0)
                   'error appeas here
                    aname(i) = .Index(Worksheets(i).Range("H2:H23393"), col, rw)  



               End With


            End With
        Next i

    '    computation
        area = 6.429571
        Sheets("Output").Activate
        Range("A1").Select
        ActiveCell.Offset(0, j).Select
        colname = Split(ActiveCell(1).address(1, 0), "$")(0)
        Columns("" & colname & ":" & colname & "").Select
        Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, _
         LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
         MatchCase:=False, SearchFormat:=False).Select

        ActiveCell.Value = "=(SUM(" & aname(1) & "," & aname(2) & "))/" & area &  ""

    Next conteo
Next j

End Sub

当日期更改为1/2/2008时，我面对错误，我该如何解决？！

When the date changes to 1/2/2008 I face with error, how can I solve it ?!

谢谢

推荐答案

由于您使用 Application.Match 和变体数据类型，在调用 .Match 期间错误不会提高，但是这些变量，如 col 和 rw 将包含一个错误类型，如果在搜索范围/数组中找不到该值。

Since you're using Application.Match and Variant data type, the error will not raise during the call to .Match, but those variables like col and rw will contain an Error type, if the value is not found in the search range/array.

此错误值将导致 TypeMismatch 错误尝试将其分配给 String 数组， aname（）。

This Error value will cause a TypeMismatch error when attempting to assign it to the String array, aname().

所以，你在中途，你只需要错误处理：

So, you're halfway there, you just need error handling:

col = .Match(j, Worksheets(i).Range("F2:F23393"), 0)
rw = .Match(CLng(conteo), Worksheets(i).Range("D2:D23393"), 0)

If Not IsError(col) and Not IsError(rw) Then
    aname(i) = .Index(Worksheets(i).Range("H2:H23393"), col, rw)
Else
    ' Do something else if there was an error

End If

或者， Dim aName（）作为Variant ，但是您可能需要进一步处理/在您的代码，以处理您将数组中的错误值。

Alternatively, Dim aName() as Variant, but then you would probably need the error handling further/elsewhere in your code, to handle the error values you're putting in to the array.

我也观察到 索引 似乎是错误的来源，并不完全不需要，因为：

Index（range_object，row_num，col_num）与 range_object.Cells（row_num，col_num）的字面上相同

所以相反，我做了：

aname(i) = CStr(Worksheets(i).Range("H2:H23393").Cells(rw, col).Value)

注意：你最初有 rw 和 col 在错误的位置为索引函数，而 rw 是一个 ROW 号码， col 是一个 COLUMN 号码。

NOTE: I also assumed that you originally have rw and col in the wrong position for the Index function, and that rw is a ROW number and col is a COLUMN number.

这篇关于Excel VBA：如何解决Index和Match函数类型不匹配的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！