RE: is there a shorter, more elegant way of doing this...

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
877
Office Version
  1. 365
Platform
  1. Windows
RE: is there a shorter, more elegant way of doing this...

...this code works great but seems way too long and cumbersome. Would anyone know how to perform this autofilter variable search operation and copy the results to the REPORT sheet to display a userform with the results as shown in this code ?
Code:
Sub CATEGORYSPENDING()
    Dim d1 As Date, d2 As Date, desval, catval As String, ws As Worksheet
    d1 = NewReport.DTPicker1.Value
    d2 = NewReport.DTPicker2.Value
    catval = NewReport.ComboBox2.Value
    Set ws = Worksheets("REPORT")
    ws.Activate
    With ws
    Range("A1:K700").Select
    Selection.Delete
    End With
    Columns("C:C").Select
    Selection.NumberFormat = "m/d/yyyy"
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Sheets("REGISTER").Range("A1:E1").AutoFilter
    With Sheets("REGISTER").Range("B1").CurrentRegion.Offset(1, 0)
    .AutoFilter Field:=2, Criteria1:=catval
    .AutoFilter Field:=3, Criteria1:=">=" & d1, Operator:=xlAnd, Criteria2:="<=" & d2
    .SpecialCells(xlVisible).Copy Sheets("REPORT").Range("A1")
    .AutoFilter
    End With
With Sheets("report")
  If ActiveSheet.Range("A1") <> "" Then
      If WorksheetFunction.CountA(Range("F:F")) = 1 Then
         Range("F1:F1").Name = "Total"
         Range("E1:E1").Name = "TotalOut"
         Range("Total").Select
         ActiveCell.Offset(2, 0).Select
         ActiveCell.Value = "=SUM(Total)+SUM(TotalOut)"
         FoodReport.Show 'IFilterReport.Show
         FoodReport.TextBox1.Value = ActiveCell.Value
         Sheets("BUDGET").Select
         FoodReport.Caption = "Category spending & NEWREPORT.Combobox2.Value"
      Else
         Range("F1:F" & Range("F1").End(xlDown).Row).Name = "Total"
         Range("E:E").Name = "TotalOut"
         Range("Total").End(xlDown).Select
         ActiveCell.Offset(2, 0).Select
         ActiveCell.Value = "=SUM(Total)+SUM(TotalOut)"
         FoodReport.Show
         FoodReport.TextBox1.Value = ActiveCell.Value
         Sheets("BUDGET").Select
         FoodReport.Caption = "Category spending" & "  -  " & NewReport.ComboBox2.Value
       End If
   Else
      MsgBox "No value found in date range"
   End If
End With
 Sheets("BUDGET").Select
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Thanks for anyone's help.
cr
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top