如何将多段VBA单元格赋值代码简化为嵌套For循环？

Got it, let's fix your VBA code simplification! The main issue with your current attempt is that you're reusing the j variable for both column indexing and the multiplier—this creates conflicts and breaks the loop logic. Here's a clean, efficient way to refactor your repetitive code using nested loops, with two straightforward approaches:

Approach 1: Using Select Case for Multipliers #

This keeps your original idea of using a case statement, but separates the multiplier variable from your column loop index:

Dim i As Integer, j As Integer
Dim multiplier As Double

For i = 0 To 4
    ' Set multiplier based on row offset (i)
    Select Case i
        Case 0: multiplier = 1   ' No multiplication for the base row
        Case 1: multiplier = 1.1
        Case 2: multiplier = 1.15
        Case 3: multiplier = 1.2
        Case 4: multiplier = 1.3
    End Select
    
    ' Loop through columns 3 to 5 to populate values
    For j = 3 To 5
        ' Map column j to rIterator's offset: j=3 → offset 2, j=4 → offset 3, j=5 → offset4
        wsA.Cells(fndRow + i, j).Value = rIterator.Offset(, j - 1).Value * multiplier
    Next j
Next i

Approach 2: Using a Multiplier Array (Cleaner & More Maintainable) #

If you prefer more concise code, store your multipliers in an array and index it with your row offset i:

Dim i As Integer, j As Integer
Dim multipliers As Variant

' Array indexes 0-4 match your row offsets 0-4
multipliers = Array(1, 1.1, 1.15, 1.2, 1.3)

For i = 0 To 4
    For j = 3 To 5
        wsA.Cells(fndRow + i, j).Value = rIterator.Offset(, j - 1).Value * multipliers(i)
    Next j
Next i

Why Your Original Attempt Broke #

In your test code, you overwrote the j variable (which is supposed to track columns 3-5) with multiplier values like 1.1. This messed up the inner loop's increment logic—instead of moving to the next column, j became a non-integer value that didn't align with your target cells. Separating the multiplier into its own variable fixes this entirely.

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