Excel VBA自动排班程序求助：循环中单元格未更新问题

Hey there, Sean! Let’s work through your problem step by step—you’re super close with the While loop idea, let’s just tweak a few things to get those cells updating and your check logic working as expected.

Why Your Cells Aren’t Updating in the Loop #

First, let’s diagnose the common culprits here:

• Manual Calculation Mode: If Excel is set to manual calculation, your =if(b2=c2,"O","X") formula won’t recalculate automatically when you change a cell value via VBA.
• Missing Calculation Trigger: Even in auto mode, sometimes VBA doesn’t force an immediate recalculation after writing to a cell, so your Check cell stays stuck on old values.
• Inefficient Random Logic: If your loop just generates random numbers without excluding the previous day’s task, you might be stuck in an infinite loop (or it takes way too long to hit a valid value), making it seem like cells aren’t updating.

Step-by-Step Solution #

1. Force Calculation in VBA #

First, make sure Excel recalculates after you assign a value. You can use Application.Calculate or recalculate specific ranges to speed things up.

2. Optimize Random Assignment (Avoid Infinite Loops) #

Instead of generating random numbers blindly, first get the previous day’s task value, then pick a random number from the allowed values (all task numbers except the previous one). This eliminates the need for endless looping.

3. Full VBA Code Example #

Here’s a tailored script for your specific scheduling scenario. I’ll include comments to explain each part so you can follow along:

Sub AutoAssignTasks()
    ' Turn off screen updating to speed up the macro (optional but helpful)
    Application.ScreenUpdating = False
    
    ' Save original calculation mode to restore later
    Dim originalCalcMode As XlCalculation
    originalCalcMode = Application.Calculation
    ' Force automatic calculation temporarily
    Application.Calculation = xlCalculationAutomatic
    
    ' --- Assign Tom's 1/5 task (previous task is in cell D2: 1/4) ---
    Dim prevTomTask As Integer
    prevTomTask = Range("D2").Value
    Dim newTomTask As Integer
    
    ' Generate random task not matching previous day's
    Do
        newTomTask = Int((3 * Rnd) + 1) ' Assuming tasks are 1, 2, 3
    Loop Until newTomTask <> prevTomTask
    
    Range("E2").Value = newTomTask
    Range("E2").Calculate ' Refresh dependent Check formula
    
    ' --- Assign Justin's 1/5 task (previous task is in cell D3: 1/4) ---
    Dim prevJustinTask As Integer
    prevJustinTask = Range("D3").Value
    Dim newJustinTask As Integer
    
    Do
        newJustinTask = Int((3 * Rnd) + 1)
    Loop Until newJustinTask <> prevJustinTask
    
    Range("E3").Value = newJustinTask
    Range("E3").Calculate
    
    ' --- Assign Mary's 1/4 task (previous task is in cell C4: 1/3) ---
    Dim prevMaryTask As Integer
    prevMaryTask = Range("C4").Value
    Dim newMaryTask As Integer
    
    Do
        newMaryTask = Int((3 * Rnd) + 1)
    Loop Until newMaryTask <> prevMaryTask
    
    Range("D4").Value = newMaryTask
    Range("D4").Calculate
    
    ' --- Assign Sam's 1/3 task (previous task is in cell B5: 1/2) ---
    Dim prevSamTask As Integer
    prevSamTask = Range("B5").Value
    Dim newSamTask As Integer
    
    Do
        newSamTask = Int((3 * Rnd) + 1)
    Loop Until newSamTask <> prevSamTask
    
    Range("C5").Value = newSamTask
    Range("C5").Calculate
    
    ' Restore original Excel settings
    Application.Calculation = originalCalcMode
    Application.ScreenUpdating = True
    
    MsgBox "Task assignments completed successfully!", vbInformation
End Sub

4. Key Tips for VBA Newbies #

• Test Small First: Run the macro one section at a time (comment out other parts) to make sure each assignment works before combining everything.
• Verify Cell References: Double-check that your Range() calls match your actual worksheet layout (e.g., if Tom is in row 3 instead of 2, adjust the range accordingly).
• Use Debugging: If something still isn’t working, add Debug.Print newTomTask inside the loop to see what numbers are being generated in the Immediate Window (press Ctrl+G in the VBA editor to open it).
• Scalable Function: For larger schedules, turn the random task logic into a reusable function to keep your code clean:

  Function GetValidRandomTask(prevTask As Integer, minTask As Integer, maxTask As Integer) As Integer
      Dim newTask As Integer
      Do
          newTask = Int((maxTask - minTask + 1) * Rnd + minTask)
      Loop Until newTask <> prevTask
      GetValidRandomTask = newTask
  End Function
  

  Call it like this: Range("E2").Value = GetValidRandomTask(Range("D2").Value, 1, 3)

内容的提问来源于stack exchange，提问作者Sean