excel-vba - Vba遍历数组

我已经在寻找答案了一段时间，但是无论尝试如何，我都会遇到不同的错误。

我有工作代码：

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

Thx作为答案，我尝试进一步实现它，并创建了如下内容：

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：对象变量或未设置块变量”
我在哪里犯错？

最佳答案

编辑-固定类型和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
'...

关于excel-vba - Vba遍历数组，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/33288283/