![在 excel 中,如何才能在单元格内容等于某个特定值时自动更改其内容](https://linux22.com/image/1348487/%E5%9C%A8%20excel%20%E4%B8%AD%EF%BC%8C%E5%A6%82%E4%BD%95%E6%89%8D%E8%83%BD%E5%9C%A8%E5%8D%95%E5%85%83%E6%A0%BC%E5%86%85%E5%AE%B9%E7%AD%89%E4%BA%8E%E6%9F%90%E4%B8%AA%E7%89%B9%E5%AE%9A%E5%80%BC%E6%97%B6%E8%87%AA%E5%8A%A8%E6%9B%B4%E6%94%B9%E5%85%B6%E5%86%85%E5%AE%B9.png)
我目前正在处理对应于一组值的一列数据(数字),我想知道如何才能自动用正确的对应值替换数据/数字?(下面提供了我的意思的图片)谢谢你的帮助!
起始数据
最后结果
答案1
在工作表更改事件中,您可以执行类似这样的操作。它假设您正在输入的列是 A,并且查找的布局是 K&J,正如您的示例所示。您可以根据需要调整范围。
Private Sub Worksheet_Change(ByVal Target As Range)
'should check you are doing entry in column A first
If Target.Column = 1 Then
Application.EnableEvents = False
Dim R1 As Range
Dim R2 As Range
Dim rngStart As Range
Dim varFind As Variant
Dim InRange As Boolean
Set R1 = Range(Target.Address)
Set R2 = Range("J:J")
Set rngStart = Range("J1")
If R2.Find(What:=Target, After:=rngStart, LookIn:=xlValues, LookAt:=xlWhole _
, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) Is Nothing Then
Application.EnableEvents = True
Set R1 = Nothing
Set R2 = Nothing
Exit Sub
Else
varFind = R2.Find(What:=Target, After:=rngStart, LookIn:=xlValues, LookAt:=xlWhole _
, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Address
Set varFind = Range(varFind)
R1.Value = varFind.Offset(0, -1)
Application.EnableEvents = True
Set R1 = Nothing
Set R2 = Nothing
Set rngStart = Nothing
Set varFind = Nothing
End If
Else
Exit Sub
End If
End Sub