excel - 错误1004遍历范围以获取最大日期

Question

我目前正在学习VBA，无法解决这一问题。我正在尝试查看每行范围(W:AC)中的最大日期，并将结果放入同一行的单元格“BU”中。我在定义要放置结果的单元格的行上遇到1004错误。因为将范围定义为W:AC，我是否收到错误消息？

Sub Max_Date()
Dim MaxDate As Date
Dim CellRange As Range
Dim Source As Worksheet: Set Source = ActiveWorkbook.Sheets("Sheet1")
Dim row As Variant
row = Source.Rows.Count

Set CellRange = Source.Range("W:AC")


For Each row In CellRange


 MaxDate = Application.WorksheetFunction.Max(CellRange)
 Range("BU").Value = MaxDate

Next row

End Sub

Answer 1

正如SJR所述，您不能在行中粘贴值。
而且，您正在查看整个范围内的最大值，而不仅仅是行。
使用整数(此处为i)遍历每一行。

这应该技巧

Sub Max_Date()
Dim MaxDate As Date
Dim i As Double
Dim Source As Worksheet: Set Source = ActiveWorkbook.Sheets("Sheet1")
Dim row As Variant
row = Source.Rows.Count

For i = 1 To row

 MaxDate = Application.WorksheetFunction.Max(Source.Range("W" & i & ":AC" & i))
 Range("BU" & i).Value = MaxDate

Next

End Sub