将数组中的字符串与范围 Excel VBA 相匹配

Question

我有以下 Public sub:

Public Sub HowToSort()
Dim i As Long, j As Long, h As Long, curCell As Range, cellBelow(1 To 10) As Variant
Dim sortOrder(1 To 10), colIsString(1 To 10) As Variant

For i = 1 To hdrCount
    'Find location of a cell
    Set curCell = Application.WorksheetFunction.Match(headRow(i), Range("a1:z1"))    ' Eventually extend outwards?
    cellBelow(i) = curCell.Offset(0, 1).Value
    If IsNumeric(cellBelow(i)) = False Then
        colIsString(i) = True
        sortOrder(i) = Application.InputBox(prompt:="Alphabetical = 'True' or Reverse Alphabetical = 'False' sorting for " & headRow(i), Type:=4)
    ElseIf IsNumeric(cellBelow(i)) = True Then
        colIsString(i) = False
        sortOrder(i) = Application.InputBox(prompt:="Ascending = True or Descending = False for " & headRow(i), Type:=4)
    Else
        MsgBox ("Program does not recognize value contained in column" & headRow(i))
        End
    End If
Next i
End Sub

它使用名为 headRow 的全局变量，其中包含工作表顶部标题行名称的字符串数组。我正在尝试使用匹配函数来查找标题所在单元格的地址：

Set curCell = Application.WorksheetFunction.Match(headRow(i), Range("a1:z1"))
cellBelow(i) = curCell.Offset(0, 1).Value

然后我想使用该地址，将其向下偏移一个单元格以查找输入的数据类型，该数据将输入到数组中colIsString。但是，.Match 函数无法正常工作，并引用“类型不匹配”错误。我不知道怎么会这样？根据我之前的研究，.Match 命令接受一个范围，然后搜索该范围以匹配单元格值。我尝试了 .Match 命令的几种变体，但没有成功。感谢您的想法...

H3lue

Answer 1

使用 Find() 代替：

Set curCell = Range("a1:z1").Find(headRow(i), , xlValues, xlWhole)
If Not curCell Is Nothing Then
    'found the header
    cellBelow(i) = curCell.Offset(0, 1).Value
    'etc etc
Else
    MsgBox "Header '" & headRow(i) & "' not found!"
End If

一旦你的子程序退出，sortOrder 和 colIsString 就会超出范围......