Vba Loop through an array

Question

I was looking for the answers for some time already, but I keep having the different errors whatever I try.

I had working code:

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

For Each x In arkwyn
....
Next

But I need to use similar approach in more complex worksheet. In addition it needs to be quick as it will be the part of more complex Update procedure that constantly keeps track of many fields in the worksheet.

Could you please take a look and help me to do it properly?

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 for the answer, I tried to implement it further and I created sth like this:

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

The idea is that the cell value is supposed to unhide certain number of rows. I keep getting "Run time error 91: Object variable or With block variable not set" Where do I make mistake?

What error are you getting with your code? – Tim Williams Oct 22 '15 at 18:59 — Tim Williams, Oct 22 '15 at 18:59
Run time error 91, object variable not set.. – torin Oct 22 '15 at 19:43 — torin, Oct 22 '15 at 19:43

Tim Williams · Accepted Answer · 2015-10-22T23:06:24.417

3

EDIT - fixed types and Rowno function

'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
'...

edited Oct 22 '15 at 23:06

answered Oct 22 '15 at 19:02

Tim Williams

154,628
8
97
125

I think the bottom half of that needs to be wrapped in a Sub. – shg Oct 22 '15 at 19:11
Yes - I assume the OP knows that, since they posted without the Sub/End sub. – Tim Williams Oct 22 '15 at 19:17
Yep, it is different Sub, I read somewhere that I should try not to use Variant variables, can you tell me if it is adequate in that case? And one more question regarding code. If I need to unhide many rows in the spreadsheet (to be found with RowNo function) how should I rewrite the code? – torin Oct 22 '15 at 19:43
1

`t1r` must be a Variant, since that is what the Array function returns. `t1` should be declared as Long, since there are more rows in Excel than an Integer can handle, and the `RowNo` function should include `As Long` at the end of the signature rather than having it default to Variant. – shg Oct 22 '15 at 20:13
"Dim t1 As Integer, r"; Does such code means, that r is also a declared variable? Does that mean the same as: "Dim t1, r as Integer" – torin Oct 22 '15 at 20:26
There r is a Variant, but that's just me being a little lazy - it should be `Dim t1 As Long, r As Long` As @shg notes, Integer can overflow with the max number of rows in Excel, but in any case it's more efficient to always use Long in place of Integer: see http://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long – Tim Williams Oct 22 '15 at 23:00

Vba Loop through an array

1 Answers1