如何使用另一个工作表中单元格内的单元格地址更改带有Excel vba的单元格的值？

Question

我们有两个工作表:

参考

2. 操作符

假设Reference表中的单元格d2包含值$a$25。

如何使用Excel VBA在操作符表中更改单元格$a$25的值(参考参考表中的d2地址)?

我已经试过了:

1. 使用变量捕获d2中的引用:

Dim NewRNG as Range
NewRNG = Range(Range("d2").value).value

运气不好。

2. 在Operator中查找参考单元格并使用找到的单元格地址

Sub Find_Cell()

Dim RNG As Range
Dim NewRNG as Range

With Sheets("Operator").Range("A:A")

Set RNG = .Find(What:=Sheets("Reference").Range("d2"), _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

NewRNG = RNG.Address

NewRNG.select

End Sub

**在这种情况下，我不能设置rng。变量

的地址

Answer 1

像这样:

Dim addr As String
addr = Sheets("Reference").Range("D2").Value   'get the cell address
Sheets("Operator").Range(addr).Value = "Test"  'pass the address to Range()

Answer 2

要定义NewRNG变量，必须使用"Set"!

Option Explicit
Sub setRange()
Dim NewRNG As Range
Set NewRNG = Range(Range("B2").Value)

Range("B5").Value = NewRNG.Address
Range("B6").Value = NewRNG.Value

Sheets("Operator").Range(Range("B2").Value) = "Test"
End Sub
Sub setRange2() ' variant including a check and error handling
Dim NewRNG As Range
On Error Resume Next
Set NewRNG = Range(Range("B2").Value)
If Err() Then
MsgBox ("You need to set a Range definition (e.g. ""B3"") in field B2!")
Exit Sub
End If
On Error GoTo 0

Range("B5").Value = NewRNG.Address
Range("B6").Value = NewRNG.Value

Sheets("Operator").Range(Range("B2").Value) = "Test"
End Sub