需要简单的通用 vba 宏脚本来根据其他单元格的值更改单元格的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9095583/
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
Need simple generic vba macro script to change value of cell based on value of other cell
提问by Srikanth Yadake
Need simple generic vba macro script to change value of cell based on value of other cell.
需要简单的通用 vba 宏脚本来根据其他单元格的值更改单元格的值。
I have values in the range from e1:e1000. In any of these cell in the range if the value is "x" then its adjacent 2 cells in respective rows should change the value to "y"
我的值在 e1:e1000 范围内。在该范围内的任何这些单元格中，如果值为“x”，则相应行中的相邻 2 个单元格应将值更改为“y”
eg: if e1 = "x" then f1 = "y" and g1 = "y"
例如：如果 e1 = "x" 然后 f1 = "y" 和 g1 = "y"
Similarly for other cells too...
同样对于其他细胞也是......
回答by Siddharth Rout
Srikanth, Reafidy has a point. Even I would prefer a formula. However if you still want a VBA code here it is.
Srikanth，Reafidy 说的有道理。即使我更喜欢公式。但是，如果您仍然需要 VBA 代码，那就是它。
USING FORMULA
使用公式
Type this in Cell F1 and G1 and simply drag it down till F1000 and G1000
在单元格 F1 和 G1 中键入此内容，然后将其向下拖动到 F1000 和 G1000
=IF(E1="X","Y","")
=IF(E1="X","Y","")
USING CODE
使用代码
Sub Sample()
Sheets("Sheet1").Range("F1:F1000").Formula = "=If(E1=""X"",""Y"","""")"
Sheets("Sheet1").Range("G1:G1000").Formula = "=If(E1=""X"",""Y"","""")"
End Sub
FOLLOW UP
跟进
Sheets("Sheet1").Range("F1:F1000").Formula = "=If(D1=""Ready"",""Ready"","""")"
OR if you do not want to use the formulas altogether then use this
或者，如果您不想完全使用这些公式，请使用它
Sub Sample()
For i = 1 To 1000
With Sheets("Sheet1")
If .Range("D" & i).Value = "Ready" Then _
.Range("F" & i).Value = "Ready" Else .Range("F" & i).Value = ""
End With
Next i
End Sub
MORE FOLLOW UP
更多跟进
thanks for reply. I tried using the one without the formula,But even this is failing. i have put the code inside Worksheet_Change function. its throwing error with 'With Sheets("Sheet1")' line, even though Worksheet_Change is in Sheet1. know why? – Srikanth Yadake 11 mins ago
谢谢您的回复。我尝试使用没有公式的那个，但即使这样也失败了。我已将代码放在 Worksheet_Change 函数中。'With Sheets("Sheet1")' 行抛出错误，即使 Worksheet_Change 在 Sheet1 中。知道为什么？– Srikanth Yadake 11 分钟前
Try this
尝试这个
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoa
If Not Intersect(Target, Range("D1:D1000")) Is Nothing Then
Application.EnableEvents = False
If Target.Value = "Ready" Then _
Target.Offset(, 2).Value = "Ready" Else Target.Offset(, 2).Value = ""
End If
LetsContinue:
Application.EnableEvents = True
Exit Sub
Whoa:
MsgBox Err.Description
Resume LetsContinue
End Sub
