Thread: Excel Cells to PPT Cells- Two Variable Ranges

Hello again,

At the moment nothing is working.

I think it's best to ignore all of the details I've asked for and imagine a situation that deals with the core of the problem.

So let's imagine I just have one column of data, and I just want to go down through that one column of data in steps of two and insert the data from each of those second rows into a new slide in PowerPoint. How would I do that?

And if that's possible, how would I then do the same thing for every 3rd row or every 4th row of that one column?

I think if I could figure out how to do this then I would be able to solve the more complex task I originally aimed to do. This seems to be the missing piece.

Thanks so much for all of your help,
ML

Wahoo!

It worked here too. I just had to change the file address and add the PowerPoint object library!
Good job :-)

And thank you, thank you, thank you...

Nice new languages too.

I'm impressed!

How did you figure out the code for that?

And how do I figure out the code for how to do the same thing but for every 2nd, 3rd, etc. row of data?

My code is always much longer than yours haha

Dear snb,

In testing the code I've found that changing the interval is not so simple. The code for intervals of three did not work on my end.

The problem lies here

   For j = 1 To UBound(sn) Step 3
     For jj = 0 To 3
       ppPres.Slides(j).Shapes(1).Table.Cell(jj \ 2 + 1, jj Mod 2 + 1).Shape.TextFrame.TextRange.Text = sn(j, jj + 1)
     Next

For example, if the worksheet has cells A1 through D9 filled, then the slide number values of j are: 1, 4, and 7.

However in this part of the

 For j = 1 To UBound(sn) Step 3
     ppPres.Slides(1).Duplicate
   Next

we only created 3 new slides, one for every 3rd row. And PowerPoint goes through trying to populate every 3rd slide with every 3rd row of Excel. But what was originally aimed for was to populate every individual slide with every 3rd row of Excel.

Therefore when running the second part of the code above, we get:

Run-time error '-2147188160 (80048240)'
Slides (unknown member): Integer out of range. 7 is not in the valid range of 1 to 4.

Because there is no 7th slide within the four slides we are working with.

So we're kind of back to the original question, which is how to populate individual slides with different intervals of data from Excel. I think it must simply not be possible to run two for next loops of varying intervals-- for slides = 1 to slides.count step 1, and for For j = 1 To UBound(sn) Step 3 -- simultaneously.

Great!!!!

That worked. Thank you very much :-D You just saved me and my whole original idea for the project I'm working on.

Unfortunately, I must have forgotten my secondary school arithmetic because I have no idea how you figured out that:

1, 4, 7, 10, 13, etc.

/3 + 1 =

1, 2, 3, 4, 5, etc.

(There must be some rounding involved somewhere in there too?)

I had been trying to think of how to find a relationship between these ranges yesterday but couldn't figure it out. What will I do for the other increments? Sorry to be dumb, but would you mind telling me how you figured out that math so I can try to figure out the other increments?

I think it's your math skills that make your codes so short and crisp

Hello there,

So I just realized that it's the same idea for other intervals:

1, 5, 9, 13, 17

/4 +1 =

1, 2, 3, 4, 5

and

1, 10, 20, 30, 40

/9 +1=

1, 2, 3, 4, 5

Etc.

Cool! But how did you figure that out?

Excuse me again for my ignorance on this.

ML

Done!

/ The division operator

\ The integer division operator - this operator divides two numbers and returns the integer result (eg. 7\4 gives a result of 1)