excel vba correct declaring ????????

[alrodopial]

Hi,
I have five markets linked to five sheets
I'm trying to place the data of these five ranges (range A1:P?? in every sheet) into a variable that has at least 5 "places"
How is called this variable that contains ranges? collection? object?
a one dimension variable: "MyTable1" declared "Public MyTable1(1 To 50, 1 To 1) As Object"
If I "Set MyTable1(1, 1) = Target" inside the worksheet_change event (in the example for the first tab)
I get it right but the data changes whenever the target is updated even if the code has not reached the line of code where I "Set MyTable1(1, 1) = Target"
This way I can not use it to compare it with the new data (new target) as they become the same
code below:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Target.Columns.Count = 16 Then
        Set ODDSrng = Target
        NoRows = ODDSrng.Rows.Count
        NoRUNNERS = NoRows - 4
       
        '
        '
        ' call macros here
        '
        '
       
        Set MyTable1(1, 1) = Target
       
        'Call assignDataToTable1
    End If
       
End Sub

I tried to copy the data I want (target) into new TEMP range and then set this range into MyTable(1,1) but I get error "object required" at row " Set MyTable1(1, 1) = TEMPrng" ( TEMPrng highlighted)
It should be easy to fix as it seems to be correct declaring or correct variables used problem
code below:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Target.Columns.Count = 16 Then
        Set ODDSrng = Target
        NoRows = ODDSrng.Rows.Count
        NoRUNNERS = NoRows - 4
       
        '
        '
        ' call macros here
        '
        '
       
        'Set MyTable1(1, 1) = Target
       
        Call assignDataToTable1
    End If
       
End Sub

Code: Select all

Public ODDSrng As Range
Public MyTable1(1 To 50, 1 To 1) As Object
Public NoRUNNERS As Integer
Public NoRows As Integer

Sub assignDataToTable1()
    Dim TEMPrng() As Range
    'Dim TEMPrng(1 To 50, 1 To 16) As Variant
    For x = 1 To NoRows
        For y = 1 To 16
            ReDim TEMPrng(x, y)
            TEMPrng(x, y) = ODDSrng(x, y)
        Next y
    Next x
   
    Set MyTable1(1, 1) = TEMPrng
   
End Sub

[alrodopial]

The below code seems to be working but I cant test it further for the moment

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Target.Columns.Count = 16 Then
        Set ODDSrng = Target
        NoRows = ODDSrng.Rows.Count
        NoRUNNERS = NoRows - 4
       
        '
        '
        ' call macros here
        '
        '
       
        Call assignDataToTable1
    End If
       
End Sub

Code: Select all

Public ODDSrng As Range
Public MyTable1(50) As Variant
Public NoRUNNERS As Integer
Public NoRows As Integer

Sub assignDataToTable1()
       
    MyTable1(1) = ODDSrng
       
End Sub

[rourkem]

Hi buddy, how did testing here went out for you?

[alrodopial]

something like the below works fine ( and not the one above)

Code: Select all

Public ODDSrngSTORED_TABLE(50) As Variant

Sub assignDataToTable1()
   
    Dim TEMParray(1 To 50, 1 To 16)
    For x = 1 To NoRows
        For y = 1 To 16
            TEMParray(x, y) = ODDSrng_BF(x, y)
        Next y
    Next x
    ODDSrngSTORED_TABLE(1) = TEMParray
       
End Sub

[alrodopial]

and then you can use it like:

Traded Amounts = ODDSrngSTORED_TABLE(1)(3,2)