• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 158
  • Last Modified:

Sub error

Hi,

When i run the below code i get the error

"Object Variable or with Variable not set"

Does anyone know how to resolve this?

Thanks
Seamus
Sub SumUpForTable2()
        Dim FoundCell As Range

        Sheets("Corporate Actions").Select
        Range("B3").Select

        Do Until ActiveCell.Value = ""
            mysheetname = ActiveCell.Text
            Sheets(mysheetname).Select
            If Range("A2").Value = "" Then

                Sheets("Corporate Actions").Select
                ActiveCell.Offset(0, 1).Value = "0"
                ActiveCell.Offset(0, 2).Value = "0"
                ActiveCell.Offset(1, 0).Select
            Else
            
   
                FoundCell = Columns("C:C").Find(What:="Grand Total", LookIn:=xlValues, _
                    lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)
            End If
            With FoundCell
                .Offset(0, 7).Select
                ActiveCell.Resize(1, 2).Select
                Selection.Copy
                Sheets("Corporate Actions").Select
                ActiveCell.Offset(0, 1).PasteSpecial Paste:=xlPasteValues
            End With
        Loop
        Range("C3:C23").Select
        Selection.NumberFormat = "#,##0"
    End Sub

Open in new window

0
Seamus2626
Asked:
Seamus2626
  • 2
  • 2
1 Solution
 
Seamus2626Author Commented:
It debugs on

           
   
                FoundCell = Columns("C:C").Find(What:="Grand Total", LookIn:=xlValues, _
                    lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)
0
 
kgerbChief EngineerCommented:
You need to use Set when setting a range variable.

Set FoundCell = Columns("C:C").Find(What:="Grand Total", LookIn:=xlValues, _
                    lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)

Open in new window

Kyle
0
 
Seamus2626Author Commented:
Perfect! Thanks Kyle

Seamus
0
 
kgerbChief EngineerCommented:
You're welcome.  I've only done that about a million times. :)

Kyle
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now