How do you loop NON-SEQUENTIALLY?

[davidm]

I need to loop through my code using a counter index which is non-systematic. For example, j = 1,3, 11,12,30,40,99 .... I intuitively attempted the following (based on j in this example) but which failed.

For i = 1 To 7
For j = Array( 1,3, 11,12,30,40,99)
Cells(j, "a") = "testing"
Next
Next

The idea is to get the loop working the first time and then apply an outer loop to run through several sheets with identical spreadsheet format.

Thanks for any help.

David

[mangesh_yadav]

Try this:

Private Sub CommandButton1_Click()

myArr = Array(1, 3, 11, 12, 30, 40, 99)

For i = 1 To 7
For j = 0 To UBound(myArr) - 1
Cells(myArr(j), "a") = "testing"
Next
Next
End Sub


Mangesh

[keepITcool]

also:

dim vRow as variant
for each vRow in Array(1,3,11)
activesheet.Cells(vRow,1) = "test"
next




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


davidm wrote :

>
> I need to loop through my code using a counter index which is
> non-systematic. For example, j = 1,3, 11,12,30,40,99 .... I
> intuitively attempted the following (based on j in this example) but
> which failed.
>
> For i = 1 To 7
> For j = Array( 1,3, 11,12,30,40,99)
> Cells(j, "a") = "testing"
> Next
> Next
>
> The idea is to get the loop working the first time and then apply an
> outer loop to run through several sheets with identical spreadsheet
> format.
>
> Thanks for any help.
>
> David

[Dave Peterson]

I don't think you want that -1 there--unless you wanted to ignore the 99.

For j = lbound(myarr) to ubound(myarr)

makes it so you don't have to worry about what the base (0 or 1) is for your
array.

mangesh_yadav wrote:
>
> Try this:
>
> Private Sub CommandButton1_Click()
>
> myArr = Array(1, 3, 11, 12, 30, 40, 99)
>
> For i = 1 To 7
> For j = 0 To UBound(myArr) - 1
> Cells(myArr(j), "a") = "testing"
> Next
> Next
> End Sub
>
> Mangesh
>
> --
> mangesh_yadav
> ------------------------------------------------------------------------
> mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
> View this thread: http://www.excelforum.com/showthread...hreadid=378912

--

Dave Peterson

[mangesh_yadav]

Hi Dave,

thanks for the suggestion. The lower bound is always what one overlooks, and simply assumes it to be 0 or 1

Mangesh

[davidm]

Thanks Mangesh and all. I also stumbled across this solution which I wiosh to share.

For i = 1 to [1:1,3:3, 11:11,12:12,30:30,40:40,99:99,100:125].Rows
Cells(j.Rows,1)="test"
Next

The last item 100:125 in the array covers the range 100-125, and for this exercise picks up Cells A100 to Cells A125 serially.

David