时间戳

首页 > 解决方案 > 编辑录制的宏以过滤三个变量

excel - 编辑录制的宏以过滤三个变量

问题描述

我有一个初始过程，我将根据条件（日期、货币组和度量）过滤数组中的值并粘贴到另一张表中。然后，我将运行代码以根据条件复制这些值并粘贴到单独的工作表中。

我想消除我过滤自己并粘贴到另一张纸上的手动位。我记录了我使用的手动过程，我的计划是插入一张同名的工作表，以便初始代码的其余部分可以正常工作。

问题是我想读取我要粘贴到的工作表上的日期，将它们用作变量，然后在我从中获取数据并粘贴到另一个工作表中的工作表上过滤我的值。

这是带有注释的代码：

Sub Actual()

Dim rw As Integer
Dim z As Integer
Dim i As Integer
Dim rpt_nm As String
Dim wb As Workbook: Set wb = ThisWorkbook
Dim wb2 As Workbook
Dim dt1, dt2, dt3 As Date
Dim ws As Worksheet
Dim TAmt, VAmt, UAmt, OAmt As Double

Worksheets("PAct").Range("G1").Activate

Let rw = ActiveCell
Let rpt_nm = Range("k1").Value
Let dt1 = Cells(rw, 1)
Let dt2 = Cells(rw + 1, 1)
Let dt3 = Cells(rw + 2, 1)

' Workbooks.Open Filename:=ThisWorkbook.Path & "\2. 2019 Legacy.xlsx"
Set wb2 = Workbooks.Open(Filename:=ThisWorkbook.Path & rpt_nm)
With wb2
    .Sheets.Add After:=.Sheets("Actual Input").Name = "VBA Input"
End With

'This is the macro I recorded in sheet I am trying to copy from
Sheets("Actual Input").Activate
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("E2").Select
ActiveCell.FormulaR1C1 = _
                 "=IF(LEN(RC[-1])=9,DATE(RIGHT(RC[-1],4),LEFT(RC[-1],1),MID(RC[-1],3,2)),DATE(RIGHT(RC[-1],4),LEFT(RC[-1],2),MID(RC[-1],4,2)))"
Range("E2").Select         'Formatting on previous lines was my clumsy attempt to try and change text formats to dates using excel
Selection.AutoFill Destination:=Range("E2:E721")
Range("E2:E721").Select
Selection.Copy
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                 :=False, Transpose:=False
Range("D1").Select
Application.CutCopyMode = False
Selection.Copy
Range("E1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("D:D").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$H$721").AutoFilter Field:=7, Criteria1:="Net"
****ActiveSheet.Range("$A$1:$H$721").AutoFilter Field:=4, Operator:= _
                 xlFilterValues, Criteria2:=Array(1, "7/31/2019", 1, "8/31/2019", 1, "9/30/2019") 'This is the place where I want to remove the specific dates selected and use variables
ActiveSheet.Range("$A$1:$H$721").AutoFilter Field:=3, Criteria1:="5M="       ‘Everytime I try and replace these values with dt1, dt2 and dt3 the filter will not select anything and there is no data****
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveSheet.Next.Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                 :=False, Transpose:=False
Range("I3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]/1000000"
Range("I3").Select
Selection.AutoFill Destination:=Range("I3:I17")
Range("I3:I17").Select
ActiveSheet.Previous.Select
ActiveSheet.Range("$A$1:$H$721").AutoFilter Field:=3, Criteria1:="1M-4.99M"
Selection.Copy
ActiveSheet.Next.Select
Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                 :=False, Transpose:=False
Range("S3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]/1000000"
Range("S3").Select
Selection.AutoFill Destination:=Range("S3:S17")
Range("S3:S17").Select
ActiveSheet.Previous.Select
ActiveSheet.Range("$A$1:$H$721").AutoFilter Field:=3, Criteria1:="1M<"
Selection.Copy
ActiveSheet.Next.Select
Range("U2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                 :=False, Transpose:=False
ActiveWindow.SmallScroll ToRight:=4
Range("AC3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]/1000000"
Range("AC3").Select
Selection.AutoFill Destination:=Range("AC3:AC17")
Range("AC3:AC17").Select
'This is where the macro recorder ends

For z = 0 To 2
    For i = 0 To 2

        wb2.Worksheets("VBA Input").Activate

        VAmt = Cells(3 + (i * 5), 9 + (z * 10))
        UAmt = Cells(4 + (i * 5), 9 + (z * 10)) + Cells(5 + (i * 5), 9 + (z * 10))
        TAmt = Cells(6 + (i * 5), 9 + (z * 10))
        OAmt = Cells(7 + (i * 5), 9 + (z * 10))

        wb.Worksheets("PAct").Activate
        Cells(rw + i, 16 + (z * 5)) = TAmt
        Cells(rw + i, 17 + (z * 5)) = VAmt
        Cells(rw + i, 18 + (z * 5)) = UAmt
        Cells(rw + i, 19 + (z * 5)) = OAmt

    Next
Next

End Sub

如果您认为有更好的方法，特别是关于录制的宏，请告诉我。

标签： excelvba

解决方案

有几点需要注意：

  • Select & Activate 方法：您要避免使用 Select/Activate 方法。通常它们是在录制宏时自动生成的，但是在 Excel 中编写 VBA 程序时，最好避免使用它们，因为它会使代码库不必要地变大并影响性能。您可以使用Ranges引用特定的单元格/范围并从中运行程序作为参考。
  • 变量和对象声明：最好声明最接近它们使用位置的变量/对象，因为读者可以轻松查看该项目的使用位置和用途。当变量使用在代码库中的几行时，巨大的声明代码块很难阅读。
  • 复制/粘贴方法：避免使用复制和粘贴。请参阅特定的范围/单元格值。例如，假设您要将值从 Sheet1 单元格 A1 复制到 Sheet2 单元格 A2。你会有这样的事情：
    Dim src As Range, dest As Range

    Set src = Sheet1.Range("A1")
    Set dest = Sheet2.Range("A2")

    dest.Value2 = src.Value2
  • 工作表引用：我更喜欢使用工作表代码名称，而不是用户可以轻松更改并破坏 VBA 过程的工作表名称。与其在这里详细介绍，不如随意查看The Spreadsheet Guru 网站，其中列出了引用工作表的几种方式。

欢迎来到 VBA 的世界！这是一门很棒的编程语言，尤其适合在 Excel 中花费大量时间的知识工作者。当您发现自己在旅途中迷失时，网络上有很多支持。

祝你好运！

推荐阅读