使用VBA循环遍历数组

Question

使用VBA循环遍历数组

4

我很久以来一直在寻找答案，但是无论我尝试什么，都会遇到不同的错误。

我有一段可用的代码：

Dim arkwyn As Variant
arkwyn = Array(1, 2, 3, "stats-obl")

For Each x In arkwyn
....
Next

但我需要在更复杂的工作表中使用类似的方法。此外，它需要快速执行，因为它将成为更复杂的更新过程的一部分，该过程不断跟踪工作表中的许多字段。

您能否请看一下并帮助我正确地完成它？

Private Function RowNo(ByVal text1 As String)
    RowNo = Columns(2).Find(text1, Lookat:=xlWhole).Row
End Function

Dim t1r As Variant 
Dim t1 As Integer

t1r = Array("1.2", "1.3", "1.4", "1.5", "1.6.2", "1.8", "1.9", "1.13.1.1", _
"1.13.1.2", "1.13.2")

For t1 = LBound(t1r) To UBound(t1r)
    Select Case UCase(Cells(RowNo(t1), 3).Value)
        Case "x"
            Rows(RowNo(t1) + 1).Hidden = False
        Case Else
            Rows(RowNo(t1) + 1).Hidden = True
    End Select
Next t1

感谢您的回答，我尝试进一步实现并创建了以下内容：

Dim ColAn As Long           
ColAn = 4
Dim YtQ1Ar As Variant       
Dim Y1q, rY1q As Long        


YtQ1Ar = Array("1.2", "1.3", "1.4", "1.5", "1.6", "1.7", "1.7.1", "1.7.2", _
"1.7.3", "1.7.4", "1.7.5", "1.7.6", "1.7.7", "1.7.8", "1.7.9", "1.7.10", "1.7.11")

    For Y1q = LBound(YtQ1Ar) To UBound(YtQ1Ar)
        rY1q = RowNo(YtQ1Ar(Y1q))
        Rows(rY1q).Hidden = (UCase(Cells(RowNo("1."), ColAn).Value) <> "TAK")
    Next Y1q

这个想法是，单元格的值应该显示特定数量的行。但我一直得到“运行时错误91：对象变量或 With 块变量未设置”的提示。我在哪里犯了错？

- torin

你的代码出现了什么错误？ - Tim Williams

运行时错误91，对象变量未设置。 - torin

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Tim Williams · Accepted Answer

编辑 - 修正了类型和Rowno函数

'Return the row for a given value, or zero if not found
Private Function RowNo(ByVal text1 As String) As Long
    Dim f As Range
    Set f = Columns(2).Find(text1, Lookat:=xlWhole)
    If Not f Is Nothing Then
        RowNo = f.Row
    Else
        RowNo = 0
    End If
End Function

'...
Dim t1r As Variant 
Dim t1 As Long, r As Long

t1r = Array("1.2", "1.3", "1.4", "1.5", "1.6.2", "1.8", "1.9", _
             "1.13.1.1", "1.13.1.2", "1.13.2")

For t1 = LBound(t1r) To UBound(t1r)
    r = RowNo(t1r(t1))
    If r > 0 Then
        Rows(r + 1).Hidden = (UCase(Cells(r, 3).Value)<>"X")
    Else
        Debug.Print "'" & t1r(t1) & "' was not found!"
    End If
Next t1
'...