vba - 在将数据从一张表复制到另一张表的 excel VBA 代码中停止数据复制

Question

我有以下代码，它记录了工作表中每天最繁忙的时间并将其添加到另一个工作表中。

Sub DailySales()
Dim dailySht As Worksheet 'worksheet storing latest          store activity
Dim recordSht As Worksheet 'worksheet to store the   highest period of each day
Dim lColDaily As Integer ' Last column of data in the store activity sheet
Dim lCol As Integer ' Last column of data in the record sheet
Dim maxCustomerRng As Range ' Cell containing the highest number of customers
Dim CheckForDups As Range ' Used to find duplicate dates on the record Sheet
Dim maxCustomerCnt As Long ' value of highest customer count

Set dailySht = ThisWorkbook.Sheets("Supermarket Data")

Set recordSht = ThisWorkbook.Sheets("Record Data")
With recordSht
    lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
With dailySht
    lColDaily = .Cells(1, .Columns.Count).End(xlToLeft).Column
    maxCustomerCnt = Application.Max(.Range(.Cells(2, 1), .Cells(2, lColDaily)))
    Set maxCustomerRng = .Range(.Cells(7, 1), .Cells(7, lColDaily)).Find(What:=maxCustomerCnt, LookIn:=xlValues)
    If Not maxCustomerRng Is Nothing Then
        Set CheckForDups = .Range(.Cells(1, 1), .Cells(1, .Columns.Count).End(x1ToLeft).Column).Find(What:=maxCustomerRng.Offset(-1, 0).Value, LookIn:x1Values)
        If CheckForDups Is Nothing Then maxCustomerRng.EntireColumn.Copy recordSht.Cells(1, lCol + 1)
    End If
End With

Set maxCustomerRng = Nothing
Set dailySht = Nothing
Set recordSht = Nothing

End Sub

我添加了以下内容以防止数据重复，即一天不应记录多个数据？

 Set CheckForDups = .Range(.Cells(1, 1), .Cells(1, .Columns.Count).End(xlToLeft).Column).Find(What:=maxCustomerRng.Offset(-1, 0).Value, LookIn:=xlValues)
            If CheckForDups Is Nothing Then maxCustomerRng.EntireColumn.Copy recordSht.Cells(1, lCol + 1)

但是，当我运行代码时出现“编译错误:语法错误”。突出显示以下行:

    Set CheckForDups = .Range(.Cells(1, 1), .Cells(1, .Columns.Count).End(x1ToLeft).Column).Find(What:=maxCustomerRng.Offset(-1, 0).Value, LookIn:x1Values)

这是第一张表的表格:

Customer data   7:00:00 AM  7:30:00 AM  8:00:00 AM  8:30:00 AM  9:00:00 AM  
Number of customers 33         37         110          250        84
Amount spent        65         50          70           85        60
Average time spent  12         10           8           17        10

有人可以告诉我我做错了什么吗？

Answer 1

您的代码中有一些拼写错误:x1Left应该是 xlLeft , x1Values应该是 xlValues , 命名参数应使用 := 指定而不是 : .

但整个陈述是不正确的，它应该是这样的:

Set CheckForDups = .Range(.Cells(1, 1), .Cells(1, .Columns.Count).End(xlToLeft)).Find(What:=maxCustomerRng.Offset(-1, 0).Value, LookIn:=xlValues)

在第二 Range参数你传递了一个列的数量，你打算传递一个单元格。所以你不需要得到 .Column属性，只是单元格本身，您可以通过删除 .Column 获得.