Variable Array based on cell value

[Eddanon]

Hello, everybody.
The following code shows an array with fixed size, with 6 values. What should i do to make its size depends on a cell value? For example, from 1 to 10?

 Dim MySlideArray As Variant
         Dim MyRangeArray As Variant
[INDENT] 'List of PPT Slides to Paste to[/INDENT]
[INDENT]   MySlideArray = Array(2, 3, 4, 5, 6, 7)[/INDENT]
[INDENT] [/INDENT]
[INDENT] 'List of Excel Ranges to Copy from. For now, assume that it's always 5 ranges[/INDENT]
[INDENT]  With tb.DataBodyRange[/INDENT]
[INDENT]     MyRangeArray = Array(Worksheets(.Cells(1, 1).Value).Range(.Cells(1, 2).Value), _[/INDENT]
[INDENT]                    Worksheets(.Cells(2, 1).Value).Range(.Cells(2, 2).Value), _[/INDENT]
[INDENT]                    Worksheets(.Cells(3, 1).Value).Range(.Cells(3, 2).Value), _[/INDENT]
[INDENT]                    Worksheets(.Cells(4, 1).Value).Range(.Cells(4, 2).Value), _[/INDENT]
[INDENT]                    Worksheets(.Cells(5, 1).Value).Range(.Cells(5, 2).Value), _[/INDENT]
[INDENT]                    Worksheets(.Cells(6, 1).Value).Range(.Cells(6, 2).Value))


'Loop through Array data
  For x = LBound(MySlideArray) To UBound(MySlideArray)
    'Copy Excel Range
        MyRangeArray(x).Copy
    
    'Paste to PowerPoint and position
      On Error Resume Next
        Set shp = myPresentation.Slides(MySlideArray(x)).Shapes.PasteSpecial(DataType:=2) 'Excel 2007-2010
        Set shp = PowerPointApp.ActiveWindow.Selection.ShapeRange 'Excel 2013
      On Error GoTo 0
[/INDENT]

 

[Chrisdontm]

I don't know VBA, but in formulas the OFFSET function can give you a dynamic range...
You may want to study it...

 

[Eddanon]

Can anybody help, please?

 

[Norie]

You create 2 arrays in that code, which one is it you want to change based on a cell value?

 

[Eddanon]

Thanks for your answer! I want to change both:
1st array is the list of slides in PPT to get each range.
2nd array is the Excel range (First column gets the name of the sheet and second column the range of the sheet)

My point is that i dont want the user to manually change the vba code based on the number of slides, i want the macro to detect automatically how the array will be

You create 2 arrays in that code, which one is it you want to change based on a cell value?

 

[Norie]

Do you need the first array?

Also, how do you determine the no of rows in of MyRangeArray?

Are you taking all the rows in the table tb?

 

[Eddanon]

Thanks for your answer!
MySlideArray would be the number of populated cells,
MyRangeArray would be the joining of column 1 with column 2 ( column 1 contains the sheet name and column 2 the range of the sheet)

and yes, all rows of the table (taking out the title)

Do you need the first array?

Also, how do you determine the no of rows in of MyRangeArray?

Are you taking all the rows in the table tb?

 

[Norie]

Does this work?

 Dim MySlideArray As Variant
         Dim MyRangeArray As Variant
 'List of PPT Slides to Paste to
   MySlideArray = Array(2, 3, 4, 5, 6, 7)
 'List of Excel Ranges to Copy from. For now, assume that it's always 5 ranges
  With tb.DataBodyRange
     MyRangeArray =.Value
  End With

'Loop through Array data
  For x = LBound(MyRangeArray,1) To UBound(MyRangeArray,1)
    'Copy Excel Range
        Sheets(MyRangeArray(x,1)).MyRangeArray(x,2).Copy
    
    'Paste to PowerPoint and position
      On Error Resume Next
        Set shp = myPresentation.Slides(MySlideArray(x)).Shapes.PasteSpecial(DataType:=2) 'Excel 2007-2010
        Set shp = PowerPointApp.ActiveWindow.Selection.ShapeRange 'Excel 2013

      On Error GoTo 0

 

[Eddanon]

unfortunately, no.
i get error 438 on line Sheets(MyRangeArray(x, 1)).MyRangeArray(x, 2).Copy

Does this work?

 Dim MySlideArray As Variant
         Dim MyRangeArray As Variant
 'List of PPT Slides to Paste to
   MySlideArray = Array(2, 3, 4, 5, 6, 7)
 'List of Excel Ranges to Copy from. For now, assume that it's always 5 ranges
  With tb.DataBodyRange
     MyRangeArray =.Value
  End With

'Loop through Array data
  For x = LBound(MyRangeArray,1) To UBound(MyRangeArray,1)
    'Copy Excel Range
        Sheets(MyRangeArray(x,1)).MyRangeArray(x,2).Copy
    
    'Paste to PowerPoint and position
      On Error Resume Next
        Set shp = myPresentation.Slides(MySlideArray(x)).Shapes.PasteSpecial(DataType:=2) 'Excel 2007-2010
        Set shp = PowerPointApp.ActiveWindow.Selection.ShapeRange 'Excel 2013

      On Error GoTo 0

 

[Norie]

What exactly do you have in the table?

Can you post some sample data?