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
– Tim Williams

2015-10-22 18:59:09 +00:00
Commented Oct 22, 2015 at 18:59 — Tim Williams
– Tim Williams, Commented Oct 22, 2015 at 18:59
Run time error 91, object variable not set..

torin
– torin

2015-10-22 19:43:11 +00:00
Commented Oct 22, 2015 at 19:43 — torin
– torin, Commented Oct 22, 2015 at 19:43

Tim Williams · Accepted Answer · 2015-10-22 23:06:24Z

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, 2015 at 23:06

answered Oct 22, 2015 at 19:02

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

shg Over a year ago

I think the bottom half of that needs to be wrapped in a Sub.

Tim Williams Over a year ago

Yes - I assume the OP knows that, since they posted without the Sub/End sub.

torin Over a year ago

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?

shg Over a year ago

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.

torin Over a year ago

"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"

|

Collectives™ on Stack Overflow

Vba Loop through an array

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related