vba array /loop help

[malleshg24]

Hi Team, 

I am practising loop , I got the answer 
But I am looking for is there any other way to bring the same answer.
with Do loop/array . Thanks in advance !!:hi:

Sub LoopPractise()

Dim i As Long, r As Long, c As Long
    r = 1: c = 1

For i = 1 To 10
    If i <= 5 Then
    Cells(r, c).Value = "*"
    r = r + 1
   
Else
        c = c - 2
    Cells(r, c).Value = "*"
    r = r + 1
End If
    c = c + 1

Next I

End Sub

[offthelip]

Your code seems to fall over when I gets to 10 so I changed it so that i only goes to 9 then I have modified it so that it uses a variant array which is a much better way of doing it because, VBA is very slow at accessing the worksheets. so if you did a loop accessing 1000 rows of data the subroutine would take a long time while doing it with variant arrays only takes milliseconds. SO this does the same as your code but uses variant arrays:

Sub test()
Dim i As Long, r As Long, c As Long
Dim inarr As Variant
inarr = Range(Cells(1, 1), Cells(9, 6))


    r = 1: c = 1


For i = 1 To 9
    If i <= 5 Then
    inarrr(r, c).Value = "*"
    r = r + 1
   
Else
        c = c - 2
    inarr(r, c).Value = "*"
    r = r + 1
End If
    c = c + 1


Next i


Range(Cells(1, 1), Cells(9, 6)) = inarr






End Sub

[malleshg24]

Thanks, for quick reply with solution!!

[malleshg24]

Hi offthelip,

I tried your code, getting error at line inarr(r,c).value = "*" stating object required, run time error '424'
plz assist why its showing error here. Thanks

Regards
Mallesh

[offthelip]

Apologies I should have tested it, there were two problems. 1st too many "R" s in "inarrr" and secondly the .value extension is not valid for a variatn arry I only changed one of them
I have also tweaked your code to shoe you how to use only one index (i) instead of i, R and C

Sub test()Dim i As Long
Dim inarr As Variant
inarr = Range(Cells(1, 1), Cells(9, 6))


For i = 1 To 9
    If i <= 5 Then
    inarr(i, i) = "*"
Else
    inarr(i, 10 - i) = "*"
    End If
Next i




Range(Cells(1, 1), Cells(9, 6)) = inarr