Printable View
Code: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
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:
Code: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
Thanks, for quick reply with solution!!
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
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
Code: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