Populate collection with values from ranges

[Spurious]

Hello all,

I have the following problem:
I got a table with 15 columns that are updated from a SQL source.
There are 10 other columns that contain manual values those I want to save to a collection, update the data from the SQL source and then retrieve the values.

My code looks like this:

Sub SaveDataToCollection()
Dim sRange As Range, PKNrs As Range, sCell As Range
Dim i As Long, test() As Variant


Set ws = ThisWorkbook.Sheets("1")
Set gcolCells = New Collection
Set PKNrs = ws.Range(ws.Cells(2, PKNrCol), ws.Cells(1, PKNrCol).End(xlDown))


For Each sCell In PKNrs
    If sCell.Row > ws.UsedRange.Rows.Count Then Exit For
    Set sRange = ws.Range(ws.Cells(sCell.Row, StartCol), ws.Cells(sCell.Row, EndCol))
    gcolCells.Add Item:=Array(sRange), Key:=CStr(sCell.Value)
    test = sRange
    For i = 1 To UBound(test)
        Debug.Print test(i)
    Next i
Next sCell
End Sub

Sub RetrieveDataFromCollection()
Dim sRange As Range, PKNrs As Range, sCell As Range


Set ws = ThisWorkbook.Sheets("1")
Set PKNrs = ws.Range(ws.Cells(2, PKNrCol), ws.Cells(1, PKNrCol).End(xlDown))


For Each sCell In PKNrs
    If sCell.Row > ws.UsedRange.Rows.Count Then Exit For
    Set sRange = ws.Range(ws.Cells(sCell.Row, StartCol), ws.Cells(sCell.Row, EndCol))
    sRange.Value = Application.Transpose(gcolCells.Item(CStr(sCell.Value)))
Next sCell
End Sub

It does not work though. When I want to check if the values are saved to an array (see the test bit), I get an error.
When I try to retrieve values (and there certainly are values in the ranges I want to save) I dont get any values.


Anyone with help to my problem?

 

[JoeMo]

A guess would be that this bit:

Item:=Array(sRange)

may be the issue. The Array function is looking for a comma-separated list of values to use as the array elements, whereas sRange is a range object. Result may be that Array(sRange) is creating a zero-length array.


</pre>

 

[Spurious]

This might actually be it, how can I work around this problem?
I cannot seem to find a solution.

 

[JoeMo]

This might actually be it, how can I work around this problem?
I cannot seem to find a solution.

After another quick look at your code, you might also look at this line:

Set PKNrs = ws.Range(ws.Cells(2, PKNrCol), ws.Cells(1, PKNrCol).End(xlDown))

This includes cells in col PKNrCol from row 2 to (but not including) the first empty cell after row 2. Is that what you want?

If yes, then resolving the Array problem, worst case, might mean adding each sRange cell as a collection item by looping through all cells in sRange.

 

[Spurious]

After another quick look at your code, you might also look at this line:

Set PKNrs = ws.Range(ws.Cells(2, PKNrCol), ws.Cells(1, PKNrCol).End(xlDown))

This includes cells in col PKNrCol from row 2 to (but not including) the first empty cell after row 2. Is that what you want?

If yes, then resolving the Array problem, worst case, might mean adding each sRange cell as a collection item by looping through all cells in sRange.

Yeah the PKNr does what I want it to do.

The thing is I dont want to add each cell to the collection because it would need a workaround to retrieve them then.

 

[JoeMo]

Yeah the PKNr does what I want it to do.

The thing is I dont want to add each cell to the collection because it would need a workaround to retrieve them then.

Maybe:

Dim vA As Variant
'Code
For Each sCell In PKNrs
    If sCell.Row > ws.UsedRange.Rows.Count Then Exit For
    Set sRange = ws.Range(ws.Cells(sCell.Row, StartCol), ws.Cells(sCell.Row, EndCol))
    vA = sRange.Value
    gcolCells.Add Item:=vA, Key:=CStr(sCell.Value)
    Erase vA
Next sCell
'More code

 

[Spurious]

Maybe:

Dim vA As Variant
'Code
For Each sCell In PKNrs
    If sCell.Row > ws.UsedRange.Rows.Count Then Exit For
    Set sRange = ws.Range(ws.Cells(sCell.Row, StartCol), ws.Cells(sCell.Row, EndCol))
    vA = sRange.Value
    gcolCells.Add Item:=vA, Key:=CStr(sCell.Value)
    Erase vA
Next sCell
'More code

I tried that earlier and it did not work.
I just got it to work by populating vA as an array in a for while.

for i=1 to 10
vA(i) = sRange(1,i).Value
next i

Rest was kept the same