vba 根据其他单元格/行输入更改特定单元格中的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10351077/
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
Changing values in a specific cell based on other cell/row inputs
提问by user1361149
Hi I'm fairly new to formulas and excel but this is one of the problems I have encountered.
嗨，我对公式和 excel 还很陌生，但这是我遇到的问题之一。
I have been using 2 conditional formatting formulas as follows
我一直在使用 2 个条件格式公式如下
=INDIRECT("I"&ROW())="Del"=INDIRECT("I"&ROW())="Sum"
=INDIRECT("I"&ROW())="Del"=INDIRECT("I"&ROW())="Sum"
Where the first formula simply highlights the row grey if del is in the I column of that row, is there also a way of making it change say the K column to 0 if column H in that row is 0?
如果 del 在该行的 I 列中，第一个公式简单地突出显示行灰色，如果该行中的列 H 为 0，是否还有一种方法可以使其更改为 0？
And for the second formula which also highlights the row another color based on sum input in the given column of that row, is it also possible to change K column of that row to match the value of H column of the given row.
对于第二个公式，它也根据该行的给定列中的总和输入突出显示该行的另一种颜色，是否也可以更改该行的 K 列以匹配给定行的 H 列的值。
I know they would be similar but I needed to make it so formula one would only zero the K column in the given row if I column had "del" and H column of the row had Zero.
我知道它们会相似，但我需要这样做，所以如果我的列有“del”并且行的 H 列为零，则公式一只会将给定行中的 K 列归零。
And for the second formula the values would only change in column k of the given row if "sum" was in the I column. Anything else needs to stay unformatted unless these changes are implemented.
对于第二个公式，如果“sum”在 I 列中，则值只会在给定行的第 k 列中发生变化。除非实施这些更改，否则其他任何内容都需要保持未格式化。
I am unable to add a formula the the cells in question as these are overwritten with an button clicked event which inputs data into this field.
我无法在有问题的单元格中添加公式，因为这些单元格被一个按钮单击事件覆盖，该事件将数据输入到该字段中。
any information is appreciated, formula or VBA.
任何信息表示赞赏，公式或 VBA。
回答by Aprillion
a) use =$I2="Del"instead of INDIRECT (where 2is the first row of the range your conditional format applies to, e.g. =$A$2:$Z$9999, or the row of the firstly selected cell of the range when you are inserting the conditional format)
a) 使用=$I2="Del"代替 INDIRECT（2条件格式适用的范围的第一行，例如=$A$2:$Z$9999，或插入条件格式时该范围的第一个选定单元格的行）
b) if you can use a new column that won't be overwritten, the formula in this new column can be:
b) 如果您可以使用不会被覆盖的新列，则此新列中的公式可以是：
=if(and(I2="Del";H2=0);0;if(I2="Sum";H2;K2))
P.S.: use ,instead of ;if your Windows > Control Panel > Region and Language > Additional settings... > List separator is set to a comma
PS：使用,而不是;如果您的Windows>控制面板>区域和语言选项>其他设置...>列表分隔符设置为一个逗号
回答by Zairja
Just adjust the Offset accordingly to change the column(s) you want.
只需相应地调整偏移量即可更改所需的列。
Dim firstCell As Range
Dim FoundCell As Range
Dim lastrow As Long
With ActiveSheet
lastrow = .Cells(.Rows.Count, "H").End(xlUp).Row
With Range("H2:H" & lastrow)
Set FoundCell = .Find(What:="3")
Set firstCell = FoundCell
Do Until FoundCell Is Nothing
'.offset(0,-1) would be the same row in Column "G"
FoundCell.Offset(0, -1).Value = 0
'if you wanted to assign the same value then do this:
' FoundCell.Offset(0, -1).Value = FoundCell.Value
Set FoundCell = .FindNext(FoundCell)
If FoundCell.Address = firstCell.Address Then
Exit Do
End If
Loop
End With
End With
