Excel VBA：使用相同单元格的内容执行覆盖单元格内容的功能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13047795/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA: Performing a function to overwrite contents of cells, using contents of those same cells
提问by CaptainProg
I am trying to run a simple operation on a column of data in Excel VBA. If a cell in column B starts with a letter before'N', nothing happens to the cell. If the cell starts with a letter 'N' or later (in the alphabet), it replaces the cell contents with "!!!!!".
我正在尝试对 Excel VBA 中的一列数据运行一个简单的操作。如果 B 列中的单元格以'N'之前的字母开头，则该单元格没有任何反应。如果单元格以字母 'N' 或以后的字母开头（在字母表中），它将用“!!!!!”替换单元格内容。
Here is my code that correctly applies this to anothercolumn:
这是我将其正确应用于另一列的代码：
Range("C1", Range("C1").End(xlDown)).Value = "=IF(LEFT(A1,1)<""N"",B1,""!!!!!"")"
This works fine, and makes column C contain the correct output. However, in order to replace column B, I then need to convert column C to literal values, and then delete all of column B.
这工作正常，并使 C 列包含正确的输出。但是，为了替换 B 列，我需要将 C 列转换为文字值，然后删除所有 B 列。
These extra steps seem a little unnecessary and cumbersome. Is there a way of applying the formula straight over column B, without needing to assign a the values to a separate column and then destroying the original column B?
这些额外的步骤似乎有点不必要和麻烦。有没有办法直接在 B 列上应用公式，而无需将值分配给单独的列然后破坏原始 B 列？
回答by mattboy
Set myRng to whatever amount of rows you need. It doesn't seem to work with a complete column.
将 myRng 设置为您需要的任何行数。它似乎不适用于完整的列。
Sub CaptainProgsSub()
Dim myRng As Range, cell As Range
Set myRng = Range("B1:B100")
For Each cell In myRng
If Left(Range("B" & cell.row), 1) > "N" Then Range("B" & cell.row) = "!!!!!"
Next cell
End Sub
