excel - 如果满足条件，则替换单元格值

Question

希望这个问题能找到你。我正在尝试使用 VBA 在 excel 中进行一些测试，但是遇到了问题。
下面是我写的代码，但它没有按照我的预期工作。

细节:

Me.txt_EN.Value is Employee Name

Me.cmb_S.Value is Shift

Me.cmb_M.Value is Month

Worksheet Name = Test

Userform Name is Userform1

以上值将通过 excel 中的用户表单添加。

这是下面的代码应该做的事情:一旦我点击了userform1中的提交按钮，它应该首先将Shift(Me.cmb_S.Value)中指定的值粘贴到一个范围内(直到这里都很好)然后如果我输入“WO”作为条目(看第一个屏幕截图1)，代码应该用“WO”更改单元格值。
示例:如果我为周二(周二)和周三(周三)选择“WO”并且我的类次为晚上(E)，它应该首先用 E 填充所有单元格(范围 E32:AI32)，然后用“WO”替换“E” "适用于所有周二和周三。 Screenshot1
Screenshot2

下面的代码有点慢，它给我的结果如屏幕截图 2 所示，它总是不包括上周二和周三(看 1/30/18 和 1/3/18，它们应该更改为“WO”)

任何解决此问题的帮助将不胜感激，还有一种方法可以添加规则，以便同一用户不能多次填写此表单？

Private Sub cmd_Submit_Click()
'select worksheet
Dim iRow As Long
Dim ws As Worksheet 
Set ws = Worksheets("RawData") 
Dim i As Integer 
Dim m As Integer 
Dim n As Integer

'find first empty row in database 
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, 
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'copy the data to the database

For i = 5 To 36 
ws.Cells(iRow, 1).Value = Me.txt_EN.Value 
ws.Cells(iRow, i).Value = Me.cmb_S.Value 
ws.Cells(iRow, 37).Value = Me.cmb_M.Value 
Next i

For m = 5 To 29
If Me.cmb_mon.Value = "WO" Then ws.Cells(iRow, m).Value = "WO"
If Me.cmb_Tue.Value = "WO" Then ws.Cells(iRow, m + 1).Value = "WO"
If Me.cmb_Wed.Value = "WO" Then ws.Cells(iRow, m + 2).Value = "WO"
If Me.cmb_thu.Value = "WO" Then ws.Cells(iRow, m + 3).Value = "WO"
If Me.cmb_Fri.Value = "WO" Then ws.Cells(iRow, m + 4).Value = "WO"
If Me.cmb_Sat.Value = "WO" Then ws.Cells(iRow, m + 5).Value = "WO"
If Me.cmb_Sun.Value = "WO" Then ws.Cells(iRow, m + 6).Value = "WO"

m = m + 6
If m > 35 Then Exit For Else
Next

MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"
Unload Me
UserForm1.Show

End Sub

Answer 1

你用

For m = 5 To 29
   ...
   m = m + 6 'here you increase counter by 6 
Next 'here you increase counter by 1 

因此，在您最后一次预期的循环之前，计数器 (m) 已经 = 32，所以它 > 然后是 29，所以循环结束。这就是为什么最后几天被排除在外的原因。
你必须稍微改变你的循环。
如果日期名称的行始终为 30，您可以使用类似这样的内容

Private Sub cmd_Submit_Click()
   'select worksheet
   Dim iRow As Long
   Dim ws As Worksheet 
   Set ws = Worksheets("RawData") 
   Dim i As Integer 
   Dim m As Integer 
   Dim n As Integer

   Dim Mon As Boolean, Tue As Boolean, Wed As Boolean, Thu As Boolean
   DIM Fri As Boolean, Sat As Boolean, Sun As Boolean
   DIM aCell as Range

   'find first empty row in database 
   iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, 
   SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual

   'copy the data to the database

   For i = 5 To 36 
      ws.Cells(iRow, 1).Value = Me.txt_EN.Value 
      ws.Cells(iRow, i).Value = Me.cmb_S.Value 
      ws.Cells(iRow, 37).Value = Me.cmb_M.Value 
   Next i

   If Me.cmb_mon.Value = "WO" Then Mon = True
   If Me.cmb_Tue.Value = "WO" Then Tue = True
   If Me.cmb_Wed.Value = "WO" Then Wed = True
   If Me.cmb_thu.Value = "WO" Then Thu = True
   If Me.cmb_Fri.Value = "WO" Then Fri = True
   If Me.cmb_Sat.Value = "WO" Then Sat = True
   If Me.cmb_Sun.Value = "WO" Then Sun = True

   For Each aCell In ws.Range("E30:AI30")
      If aCell = "Mon" And Mon Then ws.Cells(iRow, aCell.Column).Value = "WO"
      If aCell = "Tue" And Tue Then ws.Cells(iRow, aCell.Column).Value = "WO"
      If aCell = "Wed" And Wed Then ws.Cells(iRow, aCell.Column).Value = "WO"
      If aCell = "Thu" And Thu Then ws.Cells(iRow, aCell.Column).Value = "WO"
      If aCell = "Fri" And Fri Then ws.Cells(iRow, aCell.Column).Value = "WO"
      If aCell = "Sat" And Sat Then ws.Cells(iRow, aCell.Column).Value = "WO"
      If aCell = "Sun" And Sun Then ws.Cells(iRow, aCell.Column).Value = "WO"
   Next

   MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"
   Unload Me
   UserForm1.Show

   application.screenupdating = true 
   application.Calculation = xlCalculationAutomatic
End Sub

没有测试它，所以它可能需要一些调整