We have two worksheets:

1. Reference
2. Operator

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:

1. Capturing the reference in d2 with a variable:

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

No luck here.

2. Finding the reference cell in Operator and using the found cell's address

    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

CodePudding user response：

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()

CodePudding user response：

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