Create Array via function - how do I make this work?

[Spurious]

Hey,

here is my code:

Sub CompileData()
Dim DataSheet As Worksheet, CalcSheet As Worksheet
Dim Columns(), CalcState, HPGs()

Columns() = Array(1, 2, 3, 5, 6, 9, 12)
'Create HPG Array
HPGs() = CreateArray(DataSheet, Columns(0).Value)
End Sub
 
Function CreateArray(DataSheet As Worksheet, Clm As Long) As Variant
'Code
End Function

I get error 424 - Object required.

Thanks for the help.

 

[nightcrawler23]

dont use Columns as a variable.

 

[Spurious]

Ok changed it and now it still doesnt work, new code:

Sub CompileData()
Dim DataSheet As Worksheet, CalcSheet As Worksheet
Dim Clms(), CalcState, HPGs()
Dim i As Long, LastColumn As Long, LastRow As Long
 
Clms() = Array(1, 2, 3, 5, 6, 9, 12)
 
HPGs() = CreateArray(DataSheet, Clms(0).Value)
End Sub

 

[nightcrawler23]

Whats the code in your CreateArray function?
Which line does the error comes?
Is it still the same error?

 

[Spurious]

Ok changed it yet again and now it still doesnt work, new code:

Sub CompileData()
Dim DataSheet As Worksheet, CalcSheet As Worksheet
Dim Clms(), CalcState, HPGs()
Dim i As Long, LastColumn As Long, LastRow As Long
 
Clms() = Array(1, 2, 3, 5, 6, 9, 12)
i = Clms(0)
 
HPGs() = CreateArray(DataSheet, i)
End Sub

Function CreateArray(DataSheet As Worksheet, Clm As Long)
 
'Code
 
End Function

 

[pgc01]

Ok changed it and now it still doesnt work, new code:

Sub CompileData()
Dim DataSheet As Worksheet, CalcSheet As Worksheet
Dim Clms(), CalcState, HPGs()
Dim i As Long, LastColumn As Long, LastRow As Long
 
Clms() = Array(1, 2, 3, 5, 6, 9, 12)
 
HPGs() = CreateArray(DataSheet, Clms(0).Value)
End Sub

Hi

Clms() is an array, not an object, it does not have the property .Value or any other property.

 

[Spurious]

Yeah, I changed that and now I get a problem when it jumps to the function.

 

[Spurious]

Ok, I actually got a problem within the function.

Here is the code to the function:

Function CreateArray(DataSheet As Worksheet, Clm As Long) As Variant
Dim Rng As Range, Dn As Range
DataSheet.Activate
Set Rng = DataSheet.Range(Cells(2, Clm), Cells(DataSheet.Columns(Clm).Rows.Count.End(xlUp), Clm))
With CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
For Each Dn In Rng
    If Not .Exists(Dn.Value) Then
        .Add Dn.Value, [COLOR=red]CreateArray(Dn)
[/COLOR]    End If
Next
End With

Now problem with the red part. Argument not optional, how do I make it work?

 

[nightcrawler23]

you are making a recursive call. CreateArray is a fn with two arguments

 

[Spurious]

I want it to be an array though. How do I work around that. Basically, the function should start and values should be added to the function which then goes back to the main sub as an array. What am I doing wrong?