vba 根据另一个单元格值将公式写入单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15786390/
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
Write a formula into a cell depending on another cell value
提问by Daren
I was hoping to write a Macro that does a very repetitive task for me but entering VBA is harder than expected. I will learn how to program macros for excel when I have some time because it seem extremely useful, but I can't spend 5 to 12 hours this week.
我希望编写一个对我来说非常重复的任务的宏，但是进入 VBA 比预期的要难。我会在有时间的时候学习如何为 excel 编写宏，因为它看起来非常有用，但我这周不能花 5 到 12 个小时。
Maybe someone here can help!
也许这里有人可以帮忙！
I have a few excel files that follow this pattern:
我有一些遵循这种模式的 excel 文件：
Column C - Column D
--------------------
text | (empty)
number | (empty)
number | (empty)
text | (empty)
number | (empty)
text | (empty)
text | (empty)
number | (empty)
text | (empty)
number | (empty)
Where text and number alternate randomlyfor a few thousand cells. I need column D to hold, when column C is a number, the difference with previous number, otherwise it must stay blank:
几千个单元格的文本和数字随机交替。我需要D列来保存，当C列是一个数字时，与前一个数字的差异，否则它必须留空：
Column C - Column D
--------------------
text | (empty)
3 | (empty)
14 | (=C3-C2) : 11
text | (empty)
16 | (=C5-C3) : 2
text | (empty)
text | (empty)
21 | (=C8-C5) : 5
22 | (=C9-C8) : 1
So the algorithm is:
所以算法是：
var previousNumberCell
var i = 1
for all (selected) cells/rows
if (Row(i).column(C) holds number) {
Row(i).column(D).value = "=C"+i+"-"C"+previousNumberCell
previousNumberCell = i;
}
i++
End
I don't care if for the first or last cell it doesn't work.
我不在乎第一个或最后一个单元格是否不起作用。
Thank you so much for the help, or if you can point me to where I can find the answer to this.
非常感谢您的帮助，或者如果您能指出我在哪里可以找到答案。
EDIT: this is a simplified version of the problem, there are 2 things I don't know how do well with excel macros: select a cell, and tell if cell is a number... for the record, number cells have been converted from text to number format.
编辑：这是问题的简化版本，有两件事我不知道如何使用 excel 宏：选择一个单元格，然后判断单元格是否为数字...作为记录，数字单元格已被转换从文本到数字格式。
回答by David Zemens
Give this a shot:
试一试：
Sub MyMacro()
Dim rng as Range
Dim cl as Range
Dim lastNum as Range
Set rng = Range(Selection.Address) 'Make sure that your active SELECTION is correct before running the macro'
If Not rng.Columns.Count = 1 Then
MsgBox "Please select only 1 column of data at a time.",vbCritical
Exit SUb
Else:
For each cl in rng
If IsNumeric(cl.Value) Then
If lastNum Is Nothing Then
cl.Offset(0,1).Formula = "=" & cl.Address
Else:
cl.Offset(0,1).Formula = "=" & cl.Address & "-" & lastNum.Address
End If
set lastNum = cl
End If
Next
End If
End Sub
回答by Alin I
Do you require VBA?
你需要VBA吗？
Insert a new Column before column C
Column C with your values becomes column D
You might need columnheaders..
In cell C2 put: =IF(E2=0;0;SUM(E$2:$E2))this identifies rows with number
In cell E2 put: =IF(ISNUMBER(D2);1;0)this sets order for each row with a number to use next in vlookup
in cell F2 put: =IF(ISNUMBER(D2);ABS(D2-VLOOKUP(MAX($C$1:C1);$C$1:D1;2;0));"")
Autofill columns C, E and F.
In column F you get your results, except first, which is "#VALUE"
插入C柱之前的新列
与你的价值观列C柱变得d
您可能需要columnheaders ..
在C2单元格的说：=IF(E2=0;0;SUM(E$2:$E2))这个标识与行数
在E2单元格放：=IF(ISNUMBER(D2);1;0)为每一行此套顺序与一些在未来VLOOKUP使用
在单元格 F2 中输入：=IF(ISNUMBER(D2);ABS(D2-VLOOKUP(MAX($C$1:C1);$C$1:D1;2;0));"")
自动填充 C、E 和 F
列。在 F 列中，您会得到结果，但第一个除外，即“#VALUE”
回答by jan martens
Hi you can do this with an if formula and a named formula . if (isnumber ,named formula,0)
嗨，您可以使用 if 公式和命名公式来执行此操作。if (isnumber ,命名公式,0)
named formula (=lookup formula)
命名公式（=查找公式）
