编写随迭代行动态调整公式的Excel VBA程序需求及代码问询

Let's walk through what's off with your current code and get it working properly:

What's Wrong in the Original Code #

• When you concatenate & c & into the formula string, you're trying to use a Range object directly. This will pull the value of the cell instead of its row number—so if G2 has a value of 10, your formula would end up as =IF(C10>0,2,B10) instead of referencing row 2. Not what you want for dynamic row matching!
• There's an extra closing parenthesis at the end of your formula ()) instead of just )), which will throw an Excel formula error.

Corrected VBA Code #

Sub the()
    Dim c As Range
    ' Loop through each cell in column G starting at G1 down to the last non-empty cell
    For Each c In Range(Range("G1"), Range("G1").End(xlDown))
        ' Use c.Row to grab the current row number for dynamic B/C column references
        c.Formula = "=IF(C" & c.Row & ">0,2,B" & c.Row & ")"
    Next c
End Sub

Key Fixes Breakdown #

• c.Row instead of c: This pulls the row number of the current cell in column G. So when the loop hits G5, it inserts 5 into the formula, creating =IF(C5>0,2,B5)—exactly the dynamic reference you need.
• Trimmed the extra parenthesis: The original formula had an extra ) that would cause Excel to flag it as invalid. We fixed that to match the correct IF function syntax (IF(logical_test, value_if_true, value_if_false)).

Bonus: More Robust Version #

If you want to avoid issues if G1 is empty or there are blank gaps in column G, use this version to target only non-empty cells:

Sub the()
    Dim c As Range
    ' Loop through all constant-value cells in column G (skips blanks)
    For Each c In Range("G:G").SpecialCells(xlCellTypeConstants)
        c.Formula = "=IF(C" & c.Row & ">0,2,B" & c.Row & ")"
    Next c
End Sub

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