Excel VBA - 如果 B 列中的单元格包含一个值，则 A 列等于“值”但不要覆盖现有的 A 列数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45423518/
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 - If a cell in column B contains a value then column A equals "value" BUT do not overwrite existing column A data
提问by UserX
The title of the question might be a little confusing but essentially what I am trying to do is write a value to column A if Column B is not empty. This must be repeated for C through Q. The code I have now produces the desired result IF there is value in the B column. However, if there is no value for B then my replacement text will fill in all sorts of blank cells outside of the target range of A1:A. Here is the code I have:
问题的标题可能有点令人困惑，但基本上我想要做的是如果列 B 不为空，则向 A 列写入一个值。这必须对 C 到 Q 重复。如果 B 列中有值，我现在的代码会产生所需的结果。但是，如果 B 没有值，那么我的替换文本将填充 A1:A 目标范围之外的各种空白单元格。这是我的代码：
Sub Update_Column_Based_On_Column_Value_1()
On Error Resume Next
Dim ws As Worksheet
Dim lRow As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
With ws
lRow = .Range("B" & .Rows.Count).End(xlUp).Row
.Range("A1:A" & lRow).SpecialCells(xlCellTypeBlanks).Formula = "=If(B1<>"""",""NEW VALUE"","""")"
.Range("A1:A" & lRow).Value = .Range("A1:A" & lRow).Value
End With
End Sub
I am fairly new to VBA so please forgive any vagueness in my post.
我对 VBA 相当陌生，所以请原谅我帖子中的任何含糊之处。
回答by Tehscript
Instead of inserting formulas and getting their values afterwards, you can do the same logic by using pure VBA:
您可以使用纯 VBA 执行相同的逻辑，而不是插入公式并在之后获取它们的值：
Sub Update_Column_Based_On_Column_Value_1()
Dim ws As Worksheet
Dim lRow As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
With ws
lRow = .Range("B" & .Rows.Count).End(xlUp).Row
For i = 1 To lRow
If .Cells(i, 2) <> "" Then
.Cells(i, 1) = "NEW VALUE"
End If
Next i
End With
End Sub
回答by Tehscript
This formula will only work if A1 is the first blank.
这个公式只有在 A1 是第一个空格时才有效。
.Range("A1:A" & lRow).SpecialCells(xlCellTypeBlanks).Formula = "=If(B1<>"""",""NEW VALUE"","""")"
.SpecialCells(xlCellTypeBlanks)may be a discontiguous range of areas that may or may not start in row 1. You need to convert it to an xlR1C1 style formula in order to have it correctly identify the first blank row.
.SpecialCells(xlCellTypeBlanks)可能是从第 1 行开始或不从第 1 行开始的不连续区域。您需要将其转换为 xlR1C1 样式公式，以便正确识别第一个空白行。
.Range("A1:A" & lRow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=If(LEN(RC2),""NEW VALUE"", TEXT(,))"
TEXT(,)is the same as ""and you do not have to double-up the quotes within a quoted string; similarly, any positive number representing the length of a value in column B is the same as <>"".
TEXT(,)是一样的""，你不必翻倍带引号的字符串内的报价; 类似地，任何表示 B 列中值的长度的正数都与 相同<>""。
