How to address elements in variant array?

D

David

Dim rng As Range
Dim ary As Variant
Set rng = Range("A1:C10")
ary = rng.Value

How to loop through array and read/modify/delete elements

I've tried variations on the code below, but no joy so far...

For i = 1 to ubound (ary(1))
For j = 1 to Ubound(ary(2))
msgbox ary(i,j)
next
next
 
M

Mike H

Try this

Dim rng As Range
Dim ary As Variant
Set rng = ActiveSheet.Range("A1:C10")
For Each c In rng
MsgBox c.Value & " " & c.Address
Next

Obviously instaed of the message box it's possible to change the value of
each object in the range

Mike
 
B

Bernie Deitrick

David,

You are not using UBound correctly:

For i = 1 To UBound(ary, 1)
For j = 1 To UBound(ary, 2)
MsgBox ary(i, j)
Next
Next

HTH,
Bernie
MS Excel MVP
 
R

Rick Rothstein \(MVP - VB\)

You have the syntax wrong for your UBound function calls in the two For
statements...

For I = 1 To UBound(ary, 1)
For J = 1 To UBound(ary, 2)

Rick
 
D

Dave Peterson

And I'd use lbound, too:

For I = lbound(ary,1) To UBound(ary, 1)
For J = lbound(ary,2) To UBound(ary, 2)

Why remember if/when the array is 0-based or 1-based or something else based.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top