PDA

View Full Version : vba array /loop help



malleshg24
03-12-2018, 11:44 AM
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
03-12-2018, 12:01 PM
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
03-12-2018, 01:06 PM
Thanks, for quick reply with solution!!

malleshg24
03-12-2018, 07:52 PM
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
03-13-2018, 03:02 AM
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