Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Object variable or With block variable not set

    I am trying to adapt a piece code from another workbook that works fine. This one gets stuck at Intersect: The auto filter is added, it filters correctly on the correct column and then it stops. The error msg is "Object variable or With block variable not set". I have gone over it a dozen times and I cannot see where I am going wrong. Could somebody please sort me out. Many thanks.

    Code:
    Sub NotInForce()
    ‘Check Fx, Check Range
    With ActiveSheet 'Could use name of Sheet here
     .Range("$A$1:$T5000").AutoFilter Field:=15,Criteria1:="<>In Force" 
     Intersect(.Range("X1").CurrentRegion, .Range("E:G, N:O,T:T")).SpecialCells(xlCellTypeVisible).Copy
      Workbooks.Add
      'E Client Name; F Provider; G Product; N Policy No; O Status - the auto filter field; T FxAdvisor
      'Sheets.AddAfter:=Sheets(Sheets.Count)
      ActiveSheet.Paste
      ActiveSheet.UsedRange.Columns.AutoFit
      .Range("$A$1:$T$5000").AutoFilter  'removes autofilter
    End With
    End Sub
    Last edited by RetiredGeek; 2016-07-25 at 15:17. Reason: Fixed Code Tags

  2. #2
    Lounger
    Join Date
    May 2003
    Posts
    29
    Thanks
    3
    Thanked 1 Time in 1 Post
    I'm never a fan of copy and paste within macros, as humans need to do that, computer don't - that said, IF I've understood the process correctly this version should do the same thing - without an error!

    Sub NotInForce()
    'Check Fx, Check Range
    Dim LastCell As Single
    Dim NewBk As Workbook
    LastCell = ActiveSheet.Cells.SpecialCells(xlLastCell).Row

    With ActiveSheet 'Could use name of Sheet here
    .Range("$A$1:$T5000").AutoFilter Field:=15, Criteria1:="<>In Force"

    .Range("E1:G" & LastCell & ", N1:O" & LastCell & ",T1:T" & LastCell).SpecialCells(xlCellTypeVisible).Copy
    Set NewBk = Workbooks.Add
    'E Client Name; F Provider; G Product; N Policy No; O Status - the auto filter field; T FxAdvisor
    'Sheets.AddAfter:=Sheets(Sheets.Count)
    NewBk.ActiveSheet.Paste
    NewBk.ActiveSheet.UsedRange.Columns.AutoFit
    .Range("$A$1:$T$5000").AutoFilter 'removes autofilter
    End With
    End Sub

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Aidan,

    Just a minor point, but I'd suggest you use a type of Long for your row variables.

    It's not that Single won't work,, but it is a floating point type and as such requires conversion to/from long which is the native type used for row references in Excel and Excel Functions like Row(), Cells(), etc.

    HTH
    Last edited by RetiredGeek; 2016-08-02 at 09:48.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    Lounger
    Join Date
    May 2003
    Posts
    29
    Thanks
    3
    Thanked 1 Time in 1 Post
    and there was me feeling all happy that I'd at least remembered to declare the variable - I usually use LONG too!!

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Duffy,

    as an FYI....On your original code, you would receive an error if there was no intersect between cell X1.current region and the other ranges that you specified. By adding a conditional statement to check for an intersect you can avoid the error of attempting to copy an intersected area that may not exist. Note the added lines in blue:

    Code:
    Sub NotInForce()
    'Check Fx, Check Range
    With ActiveSheet 'Could use name of Sheet here
        .Range("$A$1:$T5000").AutoFilter Field:=15, Criteria1:="<>In Force"
         If Not Intersect(.Range("X1").CurrentRegion, .Range("E:G, N:O,T:T")) Is Nothing Then
            Intersect(.Range("X1").CurrentRegion, .Range("E:G, N:O,T:T")).SpecialCells(xlCellTypeVisible).Copy
            Workbooks.Add
            'E Client Name; F Provider; G Product; N Policy No; O Status - the auto filter field; T FxAdvisor
            'Sheets.AddAfter:=Sheets(Sheets.Count)
            ActiveSheet.Paste
            ActiveSheet.UsedRange.Columns.AutoFit
            .Range("$A$1:$T$5000").AutoFilter  'removes autofilter
        End If
    End With
    End Sub
    HTH,
    Maud

  6. #6
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    Duffy,

    as an FYI....On your original code, you would receive an error if there was no intersect between cell X1.current region and the other ranges that you specified. By adding a conditional statement to check for an intersect you can avoid the error of attempting to copy an intersected area that may not exist. Note the added lines in blue:

    Code:
    Sub NotInForce()
    'Check Fx, Check Range
    With ActiveSheet 'Could use name of Sheet here
        .Range("$A$1:$T5000").AutoFilter Field:=15, Criteria1:="<>In Force"
         If Not Intersect(.Range("X1").CurrentRegion, .Range("E:G, N:O,T:T")) Is Nothing Then
            Intersect(.Range("X1").CurrentRegion, .Range("E:G, N:O,T:T")).SpecialCells(xlCellTypeVisible).Copy
            Workbooks.Add
            'E Client Name; F Provider; G Product; N Policy No; O Status - the auto filter field; T FxAdvisor
            'Sheets.AddAfter:=Sheets(Sheets.Count)
            ActiveSheet.Paste
            ActiveSheet.UsedRange.Columns.AutoFit
            .Range("$A$1:$T$5000").AutoFilter  'removes autofilter
        End If
    End With
    End Sub
    HTH,
    Maud
    Hi Thanks to all for their help.

    In particular Maud who pointed out the obvious error.... I have now changed that line of the code to read:

    Code:
     Intersect(.UsedRange,.Range("E:G, I:I, J:J, N:N")).SpecialCells(xlCellTypeVisible).Copy
    An it works a treat. I have gone for .UsedRange so that next time I fiddle with the code I won't have to remember about the intersect bit.
    Last edited by RetiredGeek; 2016-08-08 at 08:52. Reason: Fixed Code Tags

Posting Permissions

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