vba 仅当单元格不为空时才使用公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16347451/
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
Formula only if cell not empty
提问by Lukasz
I'm trying to do a formula that will fill the cell value, but only once, when the cell is empty. Once filled I want the value to stay as is, even though cells that are part of the formula changed.
我正在尝试做一个公式来填充单元格值，但只有一次，当单元格为空时。一旦填充，我希望值保持原样，即使作为公式一部分的单元格发生了变化。
ie
IE
C: =VLOOKUP(Cx,'A:B,2,FALSE)
Originaly
原创
A B C
John 1 1
Bob 4 4
Jim 6 6
After Changing Jim's B value
改变吉姆的 B 值后
Jim 10 6
So I want the VLOOKUP to fetch a value only if one isn't there already. I tried to do it with an IF, but I got a circular reference error:
所以我希望 VLOOKUP 仅在一个值不存在时才获取一个值。我试图用 IF 来做，但我得到了一个循环引用错误：
=IF(C1= "", VLOOKUP(C1,'A:B,2,FALSE),C1)
采纳答案by brettdj
I think this is along the lines you want
我认为这是你想要的
- right-click your sheet tab
View Code- copy and paste in the code below
- press alt& f11to get back to Excel
If you enter say 12 in B4andA4is not blank then
如果您输入 say 12 inB4并且A4不为空，则
- If
C4is empty, it will be sety equal to 12 - if
C4has a value, it is retained
- 如果
C4为空，则 sety 等于 12 - 如果
C4有值，则保留
code
代码
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Intersect(Columns("B:B"), Target)
If rng1 Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each rng2 In rng1
If rng2.Offset(0, -1).Value <> vbNullString Then rng2.Offset(0, 1).Value = rng2.Value
Next
Application.EnableEvents = True
End Sub
回答by glh
I'm not completely sure of what you want but I think brettdj has the answer. Mine will change all empty cells in column C to a formula, to be modified to your situation, at the moment it just looks up the name and returns the column B value.
我不完全确定你想要什么，但我认为 brettdj 有答案。我的会将 C 列中的所有空单元格更改为公式，根据您的情况进行修改，目前它只是查找名称并返回 B 列值。
Please I knowthe VLOOKUP()is not required, but I wanted to show how to use it in this context.
请我知道这VLOOKUP()不是必需的，但我想展示如何在这种情况下使用它。
Sub change_empty()
Dim l As Long, r As Range
l = Me.Cells.Find("*", , , , xlByRows, xlPrevious).Row
Set r = Me.Range("C1").Resize(l).SpecialCells(xlCellTypeBlanks)
r.FormulaR1C1 = "=VLOOKUP(RC1,C1:C2,2,FALSE)"
End Sub
