将下一个空多单元格范围赋值给函数返回的变量

Question

我试图使用一个函数返回一个范围对象只有1 x 4的大小使用工作表中的下一个空行，所以我可以从一个范围转移到另一个不同的工作表上的值。不幸的是，我似乎无法解决这个问题。我试着寻找答案，但我所能找到的都是1单元解。

Private Function AssignNextRow(project As String) As Range
    Dim projectSheet As Worksheet
    Set projectSheet = Worksheets(project)
    Dim firstEmptyRow As Range
    Set firstEmptyRow = projectSheet.Range(Cells("A" & projectSheet.Rows.count) _
      .End(xlUp).Offset(1, 0), Cells("D" & projectSheet.Rows.count).End(xlUp).Offset(1, 0))
    AssignNextRow = firstEmptyRow
End Function

那么，将Range设置为变量的最佳方法是什么呢?编辑:

我已经修复了它将运行的代码，但是现在下一个工作表的范围的开始被偏移到上一个工作表的范围结束时开始。新代码如下:

Private Function AssignNextRow(project As String) As Range
    Dim projectSheet As Worksheet
    Set projectSheet = Worksheets(project)
    Dim firstEmptyRow As Range
    Set firstEmptyRow = projectSheet.Range("A" & projectSheet.Rows.count) _
      .End(xlUp).Offset(1, 0).Resize(1, 4)
    Set AssignNextRow = firstEmptyRow
End Function

我不确定在哪里解决这个问题。

编辑2:

"下一页"one_answers"最后一页"到底是什么?你可能需要提供调用AssignNextRow()的代码-Tim Williams

Private Sub DailyTransfer()
    Dim dailyRange As Variant
    dailyRange = Worksheets("Daily").Range("A3:D23").Value
    Dim x As Integer, y As Integer, z As Integer, projectName As String
    Dim entryDate As Date
    x = 1
    y = 1
    z = 1
    For x = 1 To 20
        projectName = dailyRange(x, 2)
        entryDate = dailyRange(x, 1)
        If projectName = "" Then Exit For
        If WkshtCheck(projectName) = False Then Call WkshtMake(projectName, entryDate)
        For y = 1 To 4
            Dim emptyRow As Range
            Set emptyRow = AssignNextRow(projectName)
            emptyRow(1, z).Value = dailyRange(x, y)
            If y = 1 Then y = y + 1
            z = z + 1
        Next y
    Next x
End Sub

Answer 1

只要在ColA

中始终存在一个值，就应该这样做。

Set firstEmptyRow = projectSheet.Range(Cells("A" & projectSheet.Rows.count) _
  .End(xlUp).Offset(1, 0).Resize(1,4)