循环运行时错误 91 - 看不到任何原因

Question

这可能是

很明显的事情，但我做了相当多的谷歌搜索无济于事。

首先，如果你有一个更简洁的方法，我非常高兴，我总是热衷于学习 - 这是我能想到的完成任务的最好方法。

基本上，一旦找到列，我将从另一个工作表传输数据，该工作表将位于 Range(xlDown( 的末尾并偏移 1 等。

但是目前，如果我将其运行为 For 1 到 6，它工作正常，如果我运行 1 到 7，它会给出错误，但一切都以相同的方式定义。 strSoS 已定义，但 rngSoS 始终显示为"无"，即使其他单元格工作正常？

第 1 列到第 7 列在

String 声明列表中具有 7 个标题(如前所述(，这样做的原因是工作表数据将来自可能具有不需要的额外列。

希望你能帮助解决我的问题！

Sub ColumnSearch()
Dim strDoN, strOffice, strARN, strPIN, strAN, strAT, strSoS As String
Dim rngDoN, rngOffice, rngARN, rngPIN, rngAN, rngAT, rngSoS As Range
Dim myRange As Range
Dim NumCols, i As Integer
strDoN = "Date of Notification"
strOffice = "Office Centre"
strARN = "Appeal Reference Number"
strPIN = "PIN"
strAN = "Appellant Name"
strAT = "Appeal Type"
strSoS = "SoS Decision Date"

For i = 1 To 7
    If Cells(1, i).Value = strDoN Then
        rngDoN = Cells(1, i).Address(False, False)
    ElseIf Cells(1, i).Value = strOffice Then
        rngOffice = Cells(1, i).Address(False, False)
    ElseIf Cells(1, i).Value = strARN Then
        rngARN = Cells(1, i).Address(False, False)
    ElseIf Cells(1, i).Value = strPIN Then
        rngPIN = Cells(1, i).Address(False, False)
    ElseIf Cells(1, i).Value = strAN Then
        rngAN = Cells(1, i).Address(False, False)
    ElseIf Cells(1, i).Value = strAT Then
        rngAT = Cells(1, i).Address(False, False)
    ElseIf Cells(1, i).Value = strSoS Then
        rngSoS = Cells(1, i).Address(False, False)
    End If
Next i
MsgBox rngDoN & rngOffice & rngARN & rngPIN & rngAN & rngAT & rngSoS
End Sub

Answer 1

您正在尝试将字符串地址(文本(填充到未分配的 Range 对象中。

Dim strDoN, strOffice As String, strARN As String, strPIN As String
Dim strAN As String, strAT As String, strSoS As String
Dim rngDoN As Range, rngOffice As Range, rngARN As Range
Dim rngPIN As Range, rngAN As Range, rngAT As Range, rngSoS As Range
For i = 1 To 7
    Select Case Cells(1, i).Value
        Case strDoN
            Set rngDoN = Cells(1, i)   '<~~ set the range object to this cell
        Case strOffice
            Set rngOffice = Cells(1, i)
        Case strARN
            Set rngARN = Cells(1, i)
        Case strPIN
            Set rngPIN = Cells(1, i)
        Case strAN
            Set rngAN = Cells(1, i)
        Case strAT
            Set rngAT = Cells(1, i)
        Case strSoS
            Set rngSoS = Cells(1, i)
        Case Else
            'no match - do nothing
    End Select
Next i
MsgBox rngDoN.Address(0, 0) & Chr(9) & rngOffice.Address(0, 0) & Chr(9) & _
       rngARN.Address(0, 0) & Chr(9) & rngPIN.Address(0, 0) & Chr(9) & _
       rngAN.Address(0, 0) & Chr(9) & rngAT.Address(0, 0) & Chr(9) & _
       rngSoS.Address(0, 0)

你的叙述与你真正想要完成的事情有点短。我已将范围对象设置为匹配的单元格，并将其地址返回到消息框。

rngSoS失败，因为它是唯一实际声明为范围类型变量的变量。