array lenghts
 

First of all, when you Dim a static array or ReDim a dynamic array, you
should ALWAYS provide both the upper and lower bounds. E.g.,

ReDim Arr1(1 To 10)
rather
ReDim Arr1(10)

The reason is that the lower bound of an array is determined by the Option
Base statement, if one exists. If an Option Base statement doesn't exist in
the module, 0 is used. If you copy code from one module to another, the
statement

ReDim Arr1(10)

will allocate an array of 10 elements in some modules and 11 elements in
other modules. This is an invitation for bugs. Include the lower bound.

Also, the Preserve option in ReDim is expensive and should be used ONLY when
you really do need to preserve the contents of the array. In your code, the
arrays have no data in them when you call ReDim, so you can omit the
Preserve option.

Next, your FOR loop should not have the upper and lower bounds hard coded.
Instead, use code like

For j = LBound(Arr1) To UBound(Arr1)

All that said, you can use a cell value in your ReDim statements. E.g.,

Dim Arr1() As String
Dim UB As Long
UB = Range("A1").Value
If UB = 0 Then
ReDim Arr1(0 To UB)
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"anamarie30" wrote in message
...
I would like to make these array lenghts be determined by the data or at
least to read the value from
' a small box from the spreadsheet to make the macro robust.

Dim Arr1() As String
Dim Arr2() As String
Dim Arr3() As String
Dim Arr4() As String

ReDim Preserve Arr1(56)
ReDim Preserve Arr2(20)
ReDim Preserve Arr3(56, 45)
ReDim Preserve Arr4(56)

For j = 7 To 56
Arr1(j) = Cells(j, 2)
Arr4(j) = Cells(j, 10)
Next j

For k = 1 To 20
Arr2(k) = Cells(k + 63, 2)
Next k

For z = 11 To 45
For m = 7 To 56
If Arr4(m) = "N/C" Then Arr1(m) = "Clear"
For N = 1 To 20
If Arr1(m) = Arr2(N) Then Arr3(m, z) = Cells(N + 63, z)
Next N
Next m
Next z

' Finally the results are written to the correct area in the spreadsheet
next to the component.

For z = 11 To 45
For o = 7 To 56
Cells(o, z) = Arr3(o, z)
Next o
Next z
End Sub