I fail to see anything 'dynamic' here since 'everything' is hard-coded!

Perhaps if your values were stored in a worksheet range (which could be
dynamic if desired) so they can be entered on-the-fly (ergo 'dynamic'!)
then your code won't need as much maintenance going forward...

Example 1: store values in a single row

Name: "Sheet1!MyValues"
RefersTo: =OFFSET($A$1,0,0,1,COUNTA($1:$1))
Content: [A1] 5, [B1] 6, [C1] 7, [D1] 8


Code example:

Dim vValues, n&
vValues = Range("MyValues")

For n = LBound(vValues, 2) To UBound(vValues, 2)
Debug.Print vValues(1, n)
Next 'n


Example 2: store values in a single column

Name: "Sheet1!MyValues"
RefersTo: =OFFSET($A$1,0,0,COUNTA($A:$A),1)
Content: [A1] 5, [A2] 6, [A3] 7, [A4] 8


Code example:

Dim vValues, n&
vValues = Range("MyValues")

For n = LBound(vValues) To UBound(vValues)
Debug.Print vValues(n, 1)
Next 'n


Example 3: store values in multiple rows/columns
Location: $A$1:$C$4
Name: "Sheet1!MyValues"
RefersTo: =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

Dim vValues, n&, j&
vValues = Range("MyValues")

Code example:

(column values row by row)
For n = LBound(vValues) To UBound(vValues)
For j = LBound(vValues, 2) To UBound(vValues, 2)
Debug.Print vValues(n, j)
Next 'j
Next 'n

(row values column by column)
For n = LBound(vValues, 2) To UBound(vValues, 2)
For j = LBound(vValues) To UBound(vValues)
Debug.Print vValues(n, j)
Next 'j
Next 'n

I recommend using ranges outside the data area (above or to the left).
Optionally, you can store the data on a separate sheet (which could
also be hidden, if desired) but must ref that sheet in code...

vValues = Sheets("Sheet2").Range("MyValues")

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com