Activating a different workbook and performing functionality in that workbook

Question

I have 'Workbook1' which has command buttons to perform certain operations. When a button is clicked in this workbook, it downloads a different workbook from Outlook and opens that workbook with a variable name, which is 'Workbook2'. After this, I want to set a filter in that workbook. But I'm not able to do that. I am getting 'Object Variable or With Block Variable not set' error. Below is my code.

Dim EXCELApplication As Object
Dim DefPath As Variant

Dim wb As Workbook
Dim wbName As String
Dim col2 As Long
Dim colNameF As Long
Dim colNameF1 As Long
Dim colNameF2 As Long

' Other Relevant Code Present Here'

DoEvents
    Set EXCELApplication = CreateObject("Excel.Application")
    EXCELApplication.Workbooks.Open (DefPath & strExt & ".xlsb")
    EXCELApplication.Visible = True
    EXCELApplication.Sheets("Release Level View").Activate

colNameF = Range("A8:DD8").Find(What:="Teams", LookIn:=xlValues, LookAt:=xlWhole, _
  MatchCase:=False, SearchFormat:=False).Column

colNameF1 = Range("A8:DD8").Find(What:="Items", LookIn:=xlValues, LookAt:=xlWhole, _
  MatchCase:=False, SearchFormat:=False).Column

colNameF2 = Range("A8:DD8").Find(What:="Domain", LookIn:=xlValues, LookAt:=xlWhole, _
  MatchCase:=False, SearchFormat:=False).Column

ActiveSheet.Range("$A$8:$DD$9999").AutoFilter Field:=colNameF, Criteria1:="ST Test", Operator:=xlOr, Criteria2:=""
ActiveSheet.Range("$A$8:$DD$9999").AutoFilter Field:=colNameF1, Criteria1:="Variance", Operator:=xlOr, Criteria2:=""
ActiveSheet.Range("$A$8:$DD$9999").AutoFilter Field:=colNameF2, Criteria1:="9S", Operator:=xlOr, Criteria2:=""

I'm getting the error in this particular line.

colNameF = Range("A8:DD8").Find(What:="Teams", LookIn:=xlValues, LookAt:=xlWhole, _
  MatchCase:=False, SearchFormat:=False).Column

Even if i use ActiveSheet.Range.. I'm still getting the same error. Can someone tell me what the problem is?

Thanks BK201, even when I use Set, i'm still getting the same error. Here's the complete code for your understanding.

With targetSht
    Set aCell1 = EXCELApplication.Range("A8:DD8").Find(What:="Feb", LookIn:=xlValues, LookAt:=xlWhole, _
                    MatchCase:=False, SearchFormat:=False)

    If Not aCell1 Is Nothing Then
            col2 = aCell1.Column
            SV1 = Split(Cells(col2).Address, "$")(1)
            lRow1 = .Range(SV1 & .Rows.Count).End(xlUp).Row
    End If

    colNameF = .Range("A8:DD8").Find(What:="Teams", LookIn:=xlValues, LookAt:=xlWhole, _
  MatchCase:=False, SearchFormat:=False).Column

    colNameF1 = .Range("A8:DD8").Find(What:="Items", LookIn:=xlValues, LookAt:=xlWhole, _
  MatchCase:=False, SearchFormat:=False).Column

    colNameF2 = .Range("A8:DD8").Find(What:="Domain", LookIn:=xlValues, LookAt:=xlWhole, _
  MatchCase:=False, SearchFormat:=False).Column


.Range("$A$8:$DD$9999").AutoFilter Field:=colNameF, Criteria1:="ST Test", Operator:=xlOr, Criteria2:=""
.Range("$A$8:$DD$9999").AutoFilter Field:=colNameF1, Criteria1:="Variance", Operator:=xlOr, Criteria2:=""
.Range("$A$8:$DD$9999").AutoFilter Field:=colNameF2, Criteria1:="9S", Operator:=xlOr, Criteria2:=""

.Cells(lRow1 + 1, SV1).Select
Selection.NumberFormat = "0"
SumV1 = SV1 & "9"
SumW1 = SV1 & lRow1
.Cells(lRow1 + 1, SV1).Formula = "=SUBTOTAL(9," & SumV1 & ":" & SumW1 & ")"
.Cells(lRow1 + 1, SV1).Select
Selection.Copy
End With

Windows("DS.xlsx").Activate
Set FindV = Range("A1:Z100").Find(What:="Dec Rel", LookIn:=xlValues, LookAt:=xlWhole, _
                    MatchCase:=False, SearchFormat:=False)
FindV.Offset(0, 4).NumberFormat = "0"
FindV.Offset(0, 4).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Whereever the 'cells' method is encountered, I get the same error everywhere.

WGS · Accepted Answer

Two things:

(1) Using ActiveSheet or just going straight to Range means you're running from the workbook that's calling the macro. Since you opened a new book, you have two workbooks active, but you are targeting the current workbook with the way your code is set up.

(2) This code block might cause some problem:

Set EXCELApplication = CreateObject("Excel.Application")
    EXCELApplication.Workbooks.Open (DefPath & strExt & ".xlsb")
    EXCELApplication.Visible = True
    EXCELApplication.Sheets("Release Level View").Activate

Notice: EXCELApplication.Sheets. If it works, it still is bad coding. Change it to something like this:

Dim targetBk as Workbook
Set xlApp = CreateObject("Excel.Application")
With xlApp
    Set targetBk = .Workbooks.Open (DefPath & strExt & ".xlsb")
    .Visible = True
End With
targetBk.Sheets("Release Level View").Activate

And even then, Activate and its ilk are bad things. Better to be more explicit and go with:

Dim targetBk as Workbook, targetSht As Worksheet
Set xlApp = CreateObject("Excel.Application")
With xlApp
    Set targetBk = .Workbooks.Open (DefPath & strExt & ".xlsb")
    .Visible = True
End With
Set targetSht = targetBk.Sheets("Release Level View")

With targetSht

    colNameF = .Range("A8:DD8").Find(What:="Teams").Column
    colNameF1 = .Range("A8:DD8").Find(What:="Items").Column
    colNameF2 = .Range("A8:DD8").Find(What:="Domain").Column

    .Range("$A$8:$DD$9999").AutoFilter Field:=colNameF, Criteria1:="ST Test", Operator:=xlOr, Criteria2:=""
    .Range("$A$8:$DD$9999").AutoFilter Field:=colNameF1, Criteria1:="Variance", Operator:=xlOr, Criteria2:=""
    .Range("$A$8:$DD$9999").AutoFilter Field:=colNameF2, Criteria1:="9S", Operator:=xlOr, Criteria2:=""

End With

Hope this helps!

EDIT: Regarding your comment, always remember that Find returns a Range by default. Which means that if you assign it to a variable without any other property, you are assigning a range to a variable. Needless to say, this needs Set to happen properly. See below:

Sub Test()
    Set aCell1 = Range("A1:DD8").Find(What:="Feb")
    col2 = aCell1.Column
    SV1 = Split(Cells(col2).Address, "$")(1)
    lRow1 = Range(SV1 & Rows.Count).End(xlUp).Row
    ActiveSheet.Cells(lRow1 + 1, SV1).NumberFormat = "0"
End Sub

The above works now, because col2 can properly recognize aCell1 as a Range with a .Column property. Also, your formatting line (...NumberFormat = "0") is correct.

Let us know if this helps.

EDIT2: Your usage of Cells should always be qualified. If you're using With targetSht, then SV1 = Split(.Cells(col2).Address, "$")(1). Notice the . there in .Cells. Anyway, try changing the block of code to my modification below:

With targetSht

    Set aCell1 = .Range("A8:DD8").Find(What:="Feb")

    If Not aCell1 Is Nothing Then
            col2 = aCell1.Column
            SV1 = Split(.Cells(col2).Address, "$")(1)
            lRow1 = .Range(SV1 & .Rows.Count).End(xlUp).Row
    End If

    colNameF = .Range("A8:DD8").Find(What:="Teams").Column
    colNameF1 = .Range("A8:DD8").Find(What:="Items").Column
    colNameF2 = .Range("A8:DD8").Find(What:="Domain").Column

    With .Range("$A$8:$DD$9999")
        .AutoFilterMode = False
        .AutoFilter Field:=colNameF, Criteria1:="ST Test", Operator:=xlOr, Criteria2:=""
        .AutoFilter Field:=colNameF1, Criteria1:="Variance", Operator:=xlOr, Criteria2:=""
        .AutoFilter Field:=colNameF2, Criteria1:="9S", Operator:=xlOr, Criteria2:=""
    End With

    .Cells(lRow1 + 1, SV1).NumberFormat = "0"
    SumV1 = SV1 & "9"
    SumW1 = SV1 & lRow1
    .Cells(lRow1 + 1, SV1).Formula = "=SUBTOTAL(9," & SumV1 & ":" & SumW1 & ")"
    .Cells(lRow1 + 1, SV1).Select
    Selection.Copy

End With

Let us know if this helps.

Activating a different workbook and performing functionality in that workbook

Answers (2)

Related Questions