Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Utah, USA
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro (PowerPoint 2007)

    I'm working with a table created in PowerPoint that has 9 columns and 19 rows. I want to perform a simple subtraction between two cells (4th and 5th column, 2nd row) and show the results in another cell. Does anyone have a clue how to do this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro (PowerPoint 2007)

    You can use something like this; you'll have to adjust the index of the slide and of the shape.

    With ActivePresentation.Slides(1).Shapes(3).Table
    .Cell(2, 6).Shape.TextFrame.TextRange.Text = _
    Val(.Cell(2, 4).Shape.TextFrame.TextRange.Text) - _
    Val(.Cell(2, 5).Shape.TextFrame.TextRange.Text)
    End With

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Utah, USA
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro (PowerPoint 2007)

    Hans,
    I understand slides but do not understand shapes

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro (PowerPoint 2007)

    Every object on a slide is a Shape, part of the Shapes collection.
    A table is a shape in itself, and each cell of the table is a shape too.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Utah, USA
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro (PowerPoint 2007)

    Hans,
    Thanks this works great. What do I have to do to get 0.0 rather than just 0?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro (PowerPoint 2007)

    Try
    <code>
    With ActivePresentation.Slides(1).Shapes(3).Table
    .Cell(2, 6).Shape.TextFrame.TextRange.Text = _
    Format(Val(.Cell(2, 4).Shape.TextFrame.TextRange.Text) - _
    Val(.Cell(2, 5).Shape.TextFrame.TextRange.Text), "0.0")
    End With
    </code>
    Again, modify as needed.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Utah, USA
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro (PowerPoint 2007)

    Hans,

    With the following code how would I program so cells that are empty will be skipped by the math and still show 0.0 in the answer column?

    Set a = ActivePresentation.Slides(15).Shapes(1).Table

    For j = 4 To 7 Step 3
    For i = 2 To 19
    'If IsEmpty(Val(a.Cell(i, j)) = "") Then
    a.Cell(i, j + 2).Shape.TextFrame.TextRange.Text = _
    Format(Val(a.Cell(i, j).Shape.TextFrame.TextRange.Text) - _
    Val(a.Cell(i, j + 1).Shape.TextFrame.TextRange.Text), "0.0")
    'End If
    Next i, j

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro (PowerPoint 2007)

    The macro will interpret empty cells as 0, so it appears to do what you want already. Did you run into a problem, and if so, what was it?

  9. #9
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Utah, USA
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro (PowerPoint 2007)

    The macro isn't skipping those empty cells and still performing the math. I get a negative number in the answer column.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro (PowerPoint 2007)

    Try
    <code>
    For j = 4 To 7 Step 3
    For i = 2 To 19
    If a.Cell(i, j).Shape.TextFrame.TextRange.Text = "" Or _
    a.Cell(i, j + 1).Shape.TextFrame.TextRange.Text = "" Then
    a.Cell(i, j + 2).Shape.TextFrame.TextRange.Text = "0.0"
    Else
    a.Cell(i, j + 2).Shape.TextFrame.TextRange.Text = _
    Format(Val(a.Cell(i, j).Shape.TextFrame.TextRange.Text) - _
    Val(a.Cell(i, j + 1).Shape.TextFrame.TextRange.Text), "0.0")
    End If
    Next i
    Next j</code>

  11. #11
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Utah, USA
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro (PowerPoint 2007)

    PowerPoint macro doesn't recognize "", I've tried that.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro (PowerPoint 2007)

    It works for me in PowerPoint 2002. What is the exact error message?

  13. #13
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Utah, USA
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro (PowerPoint 2007)

    Run Time Error '424':
    Object Required

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro (PowerPoint 2007)

    The code I posted is just part of a macro, like the code that you posted. I assumed that you already had code that defined the variable a.

  15. #15
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Utah, USA
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro (PowerPoint 2007)

    User error. I over wrote the line defining a. Too close to see the trees.

    Thanks, it works great now!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •