We have two worksheets:
Suppose the cell d2 in the Reference sheet contains the value $a$25 .
How to change the value of the cell $a$25 in the Operator sheet using Excel VBA (referencing the address from d2 in the Reference sheet)?
What I have tried:
Dim NewRNG as Range
NewRNG = Range(Range("d2").value).value
No luck here.
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
**in this case, i can't set rng.address to a variable
Like this:
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()
To define the NewRNG variable you have to use "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