Solved: Object Required Error

[Sirius Black]

Can't figure this out for the life of me. Why am I getting an Object Require Error with the following code:

 
Set WbAgg = ThisWorkbook
Set WsAggChrt = WbAgg.Sheets("Chart Data")
Set Period1 = WsAggChrt.Range("M2")
Set Period2 = WsAggChrt.Range("M3")
Set Period3 = WsAggChrt.Range("M4")

The error is generated with Period3. Isn't the object already set with the line Set WbAgg = ThisWorkbook?

Thanks,

Sirius

[mdmackillop]

Tests OK with me. Do you have any merged cells?

[Sirius Black]

No merged cells. It's that one cell, and for some reason VBA doesn't like my code. Not sure if this will help, but here is the code in its entirety.

 
Sub TextBox()

Dim WbAgg As Workbook, WsAggChrt As Worksheet
Dim Period1, Period2, Period3 As String
Dim PPTApp As PowerPoint.Application
Dim PPTPres As PowerPoint.Presentation
Dim PPTSld As PowerPoint.Slide
Dim Sld As Slide
Dim TxtRng As TextRange
Dim foundText As TextRange
Dim ReplacePer1, ReplacePer2, ReplacePer3 As TextRange

Set WbAgg = ThisWorkbook
Set WsAggChrt = WbAgg.Sheets("Chart Data")
Set Period1 = WsAggChrt.Range("M2")
Set Period2 = WsAggChrt.Range("M3")
Set Period3 = WsAggChrt.Range("M4")
Set PPTApp = GetObject(, "PowerPoint.Application")
Set PPTPres = PPTApp.ActivePresentation
PPTApp.ActiveWindow.ViewType = ppViewSlide
Set ReplacePer1 = PPTPres.Slides(147).Shapes(3).TextFrame.TextRange.Lines(2).Words(Start:=3, Length:=6)
Set ReplacePer2 = PPTPres.Slides(147).Shapes(3).TextFrame.TextRange.Lines(3).Words(Start:=3, Length:=6)
Set ReplacePer3 = PPTPres.Slides(150).Shapes(3).TextFrame.TextRange.Lines(2).Words(Start:=3, Length:=6)
     
    'Automated Footer - 2004 Period Replacement
    For Each Sld In PPTPres.Slides
        For Each Shp In Sld.Shapes
            If Shp.HasTextFrame Then
                Set TxtRng = Shp.TextFrame.TextRange
                Set foundText = TxtRng.Replace(FindWhat:=ReplacePer1, Replacewhat:=Period1)
                Set foundText = TxtRng.Replace(FindWhat:=ReplacePer2, Replacewhat:=Period2)
                Set foundText = TxtRng.Replace(FindWhat:=ReplacePer3, Replacewhat:=Period3)
                Do While Not (foundText Is Nothing)
                    Set TxtRng = TxtRng.Characters(foundText.Start + foundText.Length, TxtRng.Length)
                    Set foundText = TxtRng.Replace(FindWhat:=ReplacePer1, Replacewhat:=Period1)
                    Set foundText = TxtRng.Replace(FindWhat:=ReplacePer2, Replacewhat:=Period2)
                    Set foundText = TxtRng.Replace(FindWhat:=ReplacePer3, Replacewhat:=Period3)
                Loop
            End If
        Next
    Next
End Sub

Thanks,

Sirius

[mdmackillop]

I think your problem is here
[VBA]
Dim Period1, Period2, Period3 As String

[/VBA]
This is dimming only Period3 as string, the others are Dimmed as variant. Each item requires a specific "As" to Dim it to a type.
"Set" is used to set the variable to a Range etc. If you just require the value, omit the Set.
Try the following
[VBA]
Option Explicit
Sub TextBox()
Dim WbAgg As Workbook, WsAggChrt As Worksheet
Dim Period1 As String, Period2 As String, Period3 As String
Dim PPTApp As PowerPoint.Application
Dim PPTPres As PowerPoint.Presentation
Dim PPTSld As PowerPoint.Slide
Dim Sld As Slide
Dim TxtRng As TextRange
Dim foundText As TextRange
Dim ReplacePer1 As TextRange, ReplacePer2 As TextRange, ReplacePer3 As TextRange
Dim Shp
Set WbAgg = ThisWorkbook
Set WsAggChrt = WbAgg.Sheets("Chart Data")
Period1 = WsAggChrt.Range("M2")
Period2 = WsAggChrt.Range("M3")
Period3 = WsAggChrt.Range("M4")
Set PPTApp = GetObject(, "PowerPoint.Application")
Set PPTPres = PPTApp.ActivePresentation
PPTApp.ActiveWindow.ViewType = ppViewSlide
Set ReplacePer1 = PPTPres.Slides(147).Shapes(3).TextFrame.TextRange.Lines(2).Words(Start:=3, Length:=6)
Set ReplacePer2 = PPTPres.Slides(147).Shapes(3).TextFrame.TextRange.Lines(3).Words(Start:=3, Length:=6)
Set ReplacePer3 = PPTPres.Slides(150).Shapes(3).TextFrame.TextRange.Lines(2).Words(Start:=3, Length:=6)

'Automated Footer - 2004 Period Replacement
For Each Sld In PPTPres.Slides
For Each Shp In Sld.Shapes
If Shp.HasTextFrame Then
Set TxtRng = Shp.TextFrame.TextRange
Set foundText = TxtRng.Replace(FindWhat:=ReplacePer1, Replacewhat:=Period1)
Set foundText = TxtRng.Replace(FindWhat:=ReplacePer2, Replacewhat:=Period2)
Set foundText = TxtRng.Replace(FindWhat:=ReplacePer3, Replacewhat:=Period3)
Do While Not (foundText Is Nothing)
Set TxtRng = TxtRng.Characters(foundText.Start + foundText.Length, TxtRng.Length)
Set foundText = TxtRng.Replace(FindWhat:=ReplacePer1, Replacewhat:=Period1)
Set foundText = TxtRng.Replace(FindWhat:=ReplacePer2, Replacewhat:=Period2)
Set foundText = TxtRng.Replace(FindWhat:=ReplacePer3, Replacewhat:=Period3)
Loop
End If
Next
Next
End Sub

[/VBA]

[Sirius Black]

When I dim Period1, Period2 & Period3 as String, I still get an Object Required error, this time pointing to my Set Period1 statement. I changed the dim from string to variant and the code worked without any problems.

Thanks for all of your help!

Sirius

[mdmackillop]

Glad to help.
Make sure that you use Option Explicit, which can help identify this type of error.