Hey there, let's dig into why you're hitting that Run-time error '13': Type mismatch when using Application.VLookup, Match, or Index with your public Variant array. I've run into this exact issue when optimizing VBA code from slow cell/Offset operations to arrays, so here's what's likely going wrong and how to fix it:

Common Causes & Fixes #

1. Your Array is 2D (But Match Expects a 1D Array) #

When you assign a worksheet range to a Variant array (like wsrgcmes = Range("A1:B1000").Value), Excel returns a 2D array—even if the range is a single column or row. Functions like Application.Match require a 1D array for the lookup_array parameter, which triggers a type mismatch if you pass the full 2D array directly.

Fix: Convert the Target Column to a 1D Array

Extract the column you want to search into a 1D array first:

' Convert the first column of your 2D array to a 1D array
Dim lookupCol() As Variant
ReDim lookupCol(LBound(wsrgcmes, 1) To UBound(wsrgcmes, 1))

Dim i As Long
For i = LBound(wsrgcmes, 1) To UBound(wsrgcmes, 1)
    lookupCol(i) = wsrgcmes(i, 1) ' Adjust the column index as needed
Next i

' Now use Match with the 1D array
Dim matchPos As Variant
matchPos = Application.Match(yourLookupValue, lookupCol, 0)

2. Mismatched Data Types Between Lookup Value & Array Elements #

This is especially common with dates. Excel stores dates as Double values (serial numbers), but your lookup value might be a VBA Date type or even a text string. Mixing these types causes the type mismatch error.

Fix: Align Data Types

First, check the types with Debug.Print to confirm:

Debug.Print TypeName(yourLookupValue), TypeName(wsrgcmes(1, 1))

• If your lookup value is a Date, convert it to a Double to match Excel's stored date format: CDbl(yourLookupValue)
• If your array has text-formatted dates, convert your lookup value to a string: CStr(yourLookupValue)

For date-specific cases, here's how to adjust the 1D array conversion:

For i = LBound(wsrgcmes, 1) To UBound(wsrgcmes, 1)
    ' Ensure array elements are stored as Double (Excel's date format)
    If IsDate(wsrgcmes(i, 1)) Then
        lookupCol(i) = CDbl(wsrgcmes(i, 1))
    Else
        lookupCol(i) = "" ' Handle empty cells to avoid errors
    End If
Next i

' Match using the converted date value
matchPos = Application.Match(CDbl(targetDate), lookupCol, 0)

3. Uninitialized or Invalid Array Elements #

If your public array wsrgcmes wasn't properly populated (e.g., the source range was empty, or you forgot to assign it before using it), it might be Empty or contain error values like #N/A, which will trigger type mismatches.

Fix: Validate the Array First

Add a check to ensure the array is initialized before using it:

If IsEmpty(wsrgcmes) Then
    MsgBox "Array is not populated!"
    Exit Sub
End If

' Also, handle error values in the array if needed
For i = LBound(wsrgcmes, 1) To UBound(wsrgcmes, 1)
    If IsError(wsrgcmes(i, 1)) Then
        lookupCol(i) = "" ' Replace errors with a safe value
    Else
        lookupCol(i) = wsrgcmes(i, 1)
    End If
Next i

Full Working Example #

Here's a complete snippet that ties this all together for your daily report scenario:

Public wsrgcmes As Variant

Sub GenerateDailyReport()
    ' Populate the array from your data worksheet
    wsrgcmes = ThisWorkbook.Worksheets("RawData").Range("A1:B1000").Value
    
    ' Target date to look up (e.g., today's date)
    Dim targetDate As Date
    targetDate = Date
    
    ' Validate array initialization
    If IsEmpty(wsrgcmes) Then
        MsgBox "No data found in the source range!"
        Exit Sub
    End If
    
    ' Convert date column to 1D array (aligned to Excel's Double date format)
    Dim dateCol() As Variant
    ReDim dateCol(LBound(wsrgcmes, 1) To UBound(wsrgcmes, 1))
    Dim i As Long
    For i = LBound(wsrgcmes, 1) To UBound(wsrgcmes, 1)
        If IsDate(wsrgcmes(i, 1)) Then
            dateCol(i) = CDbl(wsrgcmes(i, 1))
        Else
            dateCol(i) = ""
        End If
    Next i
    
    ' Find the position of the target date
    Dim matchPos As Variant
    matchPos = Application.Match(CDbl(targetDate), dateCol, 0)
    
    ' Retrieve the corresponding value if found
    If Not IsError(matchPos) Then
        Dim dailyValue As Variant
        dailyValue = Application.Index(wsrgcmes, matchPos, 2)
        MsgBox "Today's report value: " & dailyValue
    Else
        MsgBox "No data found for today's date."
    End If
End Sub

Key Takeaways #

• Always convert 2D range arrays to 1D arrays when using Match
• Align data types (especially critical for dates)
• Validate your array is populated and handle invalid elements before running lookups

内容的提问来源于stack exchange，提问作者Datalyzer