error on loop array of days

Question

I test this code to loop if one date is into my array. with the example on the column A, nothing problem, the result message is 3 and in the immediate window I saw (01/01/2015,06/01/2015,02/02/2015) to test some case when only 1 day is into array, I delete the 2 day (A3,A4) like column C. the result message is 1 but i received error on rule For Each i In arfest (error runtime 13, type not corresponding) and if I delete also the value on A2, I retrieve the msg with 1 and the same error previous example.

Sub dada_click()
    Dim arfest As Variant
    Dim i As Variant
    UR = Sheets("Fest").Cells(Rows.Count, 1).End(xlUp).Row
    If UR < 2 Then UR = 2
    arfest = Sheets("Fest").Range("A2:A" & UR)
    MsgBox (Application.CountA(arfest))
    For Each i In arfest
       Debug.Print i
    Next i
End Sub

What's wrong?!?!? thank

sous2817 · Accepted Answer · 2015-11-06 15:13:36Z

1

With only 1 object in your range, set a break point on your loop start. You'll see that arfest is a single date. There's nothing to loop through. This highlights one of the issues of dimming everything as a variant and letting the compiler decide what the variable is holding. Be more specific in your declaration. If you know arfest is a range, tell the compiler that! Same goes for i. Try this and see if it gets you past your issue:

Sub test()
    Dim arfest As Range
    Dim i As Range
    UR = Sheets("Fest").Cells(Rows.Count, 1).End(xlUp).Row
    If UR < 2 Then UR = 2
    Set arfest = Sheets("Fest").Range("A2:A" & UR)

    For Each i In arfest
       Debug.Print i
    Next i
End Sub

Again, step through the my code and your code and see the difference in how arfest is handled in each situation.

answered Nov 6, 2015 at 15:13

sous2817

3,9602 gold badges37 silver badges37 bronze badges

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

1 Comment

Fabrizio Over a year ago

ya it's right, for me stupidly an array is a variant, but is actually correct to define it, I'll make a node. Thank.

Collectives™ on Stack Overflow

error on loop array of days

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related