View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
 
Posts: n/a
Default how to convert an array

Hello,

A suggestion for a UDF:
Option Explicit

Function one_dim_array(rI As Range) As Variant
'Converts an argument array of frequencies and values into
'a one-dimensional array.
'Example:
'3 25
'1 10
'4 12
'6 7
'will become 25 25 25 10 12 12 12 12 7 7 7 7 7 7.
'Remember to select sufficient count of cells for result
'and to enter function as array-formula (CTRL + SHIFT + ENTER).
Dim vR As Variant
Dim r As Range
Dim b As Boolean
Dim lF As Long, i As Long, j As Long

With Application.Caller

ReDim vR(1 To .Rows.Count, _
1 To .Columns.Count)
i = 1
j = 1
b = True
For Each r In rI
If b Then
lF = r.Value
Else
Do While lF 0
vR(i, j) = r.Value
j = j + 1
If j .Columns.Count Then
j = 1
i = i + 1
End If
lF = lF - 1
Loop
End If
b = Not b
Next r

End With

one_dim_array = vR

End Function

HTH,
Bernd