VBA在非空白和空白之间找到空单元格自动填充到日历时跳过周末 [英] VBA find empty cells between non-blanks & skip weekends when auto populate to calendar

问题描述

我正在尝试构建一个员工休假跟踪器，以将数据从一个摘要表自动填充到Excel内置的日历中.

I'm trying to build a staff vacation tracker to auto populate the data from one summary sheet to a calendar built in Excel.

我对此有一些疑问.

正确，在填充日历时，我希望代码在周末跳过.

Firsy, when populating to calendar, I want the code to skip over weekends.

第二，如果多个员工在同一日期休假.我希望能够找到日期下方的下一个空白行.

Second, if multiple employee takes vacation on the same date. I want to be able to find the next blank row under the date.

日历如下所示:

该日期下方还有其他日期，因此我不使用rows.count查找最后一个空行.

There're other dates below the date so I coudn't use the rows.count to find the last empty row.

以下代码是if语句的一部分，用于填充多天.

The following code is part of and if statement to populate multiple days.

我的代码的其他部分可以在这篇文章中找到

Other part of my code could be found in this post

根据开始日期和结束日期的不同自动将多个条目填充到日历中

我正在尝试解决上面列出的两个问题.

I'm trying to resolve the two quesions listed above.

我是编程的初学者.层太多了，我不知道我的代码出了什么问题.

I'm a beginner in programming. There're too many layers and I coudn't figure out what's wrong with my code.

非常感谢您的帮助！

 For i = 1 To TotalDaysOff

        If Weekday(startDate + (i - 2), vbMonday) < 6 Then

            With Sheets(sSheet)
                Set R = .Range("A1:H58").Find(startDate + (i - 2))
                If Not R Is Nothing Then

                    If IsEmpty(R.row + 1) Then
                        Sheets(sSheet).Cells(R.row + 1, R.Column).Value = Employee & " " & Reason & " " & Time

                    Else
                        Sheets(sSheet).Cells(R.row + 2, R.Column).Value = Employee & " " & Reason & " " & Time

                    End If


                 End If

            End With

         Else
         End If

   Next i

End If

推荐答案

不确定这是否是答案..但我认为以下内容可以帮助

not sure if this is the answer.. but i think the below can help

计算最后一个单元格与下一个非空白单元格之间的行中空白单元格的数量

如果可以计数，并且计数返回零，则可能需要找到另一个单元格来填充数据.如果计数大于零，则可以尝试使用IF语句定位单元格，如同一问题中所述.

If you can count and if the count returns Zero then you may need to find another cell to fll the data.. If the count is greater than Zero, then you can try locating the cell by IF statements, as mentioned in the same question.

这篇关于VBA在非空白和空白之间找到空单元格自动填充到日历时跳过周末的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！