请问能否简化这段VBA代码？如何将其简化为单语句？

Absolutely! That repeated cell assignment code can be condensed into a single clean statement—no need to write a line for every B-Y cell pair. Here are two straightforward approaches, including the single-line solution you're after:

Single-Line Solution (Using A1-Style Formula) #

This works because Excel automatically adjusts relative row references when you assign a formula to a multi-cell range:

On Error Resume Next
Range("B11:B16").Formula = "=Y11"
' If you need to extend the range later (e.g., B11 to B20), just update the range: Range("B11:B20").Formula = "=Y11"

When you run this, Excel will set B11=Y11, B12=Y12, B13=Y13, and so on—exactly what your original code does, but in one line.

Alternative Single-Line Solution (Using R1C1-Style Formula) #

If you prefer using relative column references (useful if you might shift columns later), this is a great option. Since column B is 23 columns to the left of column Y, we use RC[23] to reference the cell 23 columns to the right in the same row:

On Error Resume Next
Range("B11:B16").FormulaR1C1 = "=RC[23]"

This achieves the same result, and it’s more flexible if you ever need to adjust the column offset instead of hardcoding column Y.

Quick Note on On Error Resume Next #

While your original code includes this, just a heads-up: it suppresses all errors, which can hide bugs (like if the range doesn’t exist). If you’re using this intentionally, that’s fine—but consider adding targeted error handling after the assignment if you need to catch issues.

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