CODEWITHSUNDEEP
excelvbareference
kirk
kirk

Reputation: 307

Set range equal to another range dynamically using VBA

I would like to set a range of cells with reference to a named range name1 equal to a similarly sized range of cells on another Excel sheet also referencing a named range name2. I would like the cells in Sheet1 to equal whatever the corresponding cell in Sheet2 currently equals, and therefore I cannot use the .value property.

Sheets("Sheet1").Range("name1").Offset(0, 1).Resize(15, 5) = Sheets("Sheet2").Range("name2").Offset(0, 1).Resize(15, 5).value

Except, that I do not want to use value. Is there a simple way like this to do what I require? I have searched some forums but cannot find a good way to do this. Do I need to use a For each and R1C1 naming? To reiterate - the cells on sheet1 should equal whatever the value is of the relative cell on sheet2 (ranges are the same size). So, eg. cell Sheet1!A1 has the formula =Sheet2!A1.

Upvotes: 1

Views: 22872

Answers (2)

to StackOverflow
to StackOverflow

Reputation: 124696

You could try something like the following:

Sheets("Sheet1").Range("name1").Offset(0, 1).Resize(15, 5).FormulaR1C1 = "=Sheet2!R[0]C[0]"

Update

If the ranges (name1 and name2) are at different positions, you will need to adjust the formula accordingly:

Dim nRowOffset As Long
Dim nColOffset As Long
Dim sFormula As String
nRowOffset = Sheets("Sheet2").Range("name2").Row - Sheets("Sheet1").Range("name1").Row
nColOffset = Sheets("Sheet2").Range("name2").Column - Sheets("Sheet1").Range("name1").Column
sFormula = "=Sheet2!R[" & nRowOffset & "]C[" & nColOffset & "]"
Sheets("Sheet1").Range("name1").Offset(0, 1).Resize(15, 5).FormulaR1C1 = sFormula

Upvotes: 1

NickSlash
NickSlash

Reputation: 5077

I've only tested this a little so it might not be all that robust.

Note This sub needs to be placed in a new (or existing) module, and not in any of the sheet or thisworkbook modules.

It's a macro, and so cannot be called from the worksheet as a UDF. Also, as it has arguments it cannot be directly called.

To use the code you need to create another sub to call this it for you, or call it directly from the immediate window.

Sub RunCode()
    Main "Name1", "Name2" ' you could run this line in the immediate/debug window
End Sub

The sub RunCode should be available in the macros menu on in your workbook.

Sub Main(ByVal Name1 As String, ByVal Name2 As String)
Dim Cell As Long
Dim Range1 As Range: Set Range1 = ThisWorkbook.Names(Name1).RefersToRange
Dim Range2 As Range: Set Range2 = ThisWorkbook.Names(Name2).RefersToRange
' check to make sure Name1 and Name2 are the same size
If Range1.Cells.Count = Range2.Cells.Count Then 
    If Range1.Rows.Count = Range2.Rows.Count Then
        If Range1.Columns.Count = Range2.Columns.Count Then
            ' populate the cells with the formula
            For Cell = 1 To Range1.Cells.Count
                Range2.Cells(Cell).Formula = "=" & Range1.Worksheet.Name & "!" & Range1.Cells(Cell).Address
            Next Cell
        End If
    End If
End If

End Sub

If you wanted slightly more customizable interface to the function, then the following code should help. Running the RunCode2 macro will prompt you to enter the two names to pass to Main

Public Function nameExists(ByVal Name As String) As Boolean
Dim Result As Boolean: Result = fasle
Dim Item As Variant
For Each Item In ThisWorkbook.Names
    If Item.Name = Name Then
        Result = True
        Exit For
    End If
Next Item
nameExists = Result
End Function

Sub RunCode2()
Dim Response As Variant
Dim Name1, Name2 As String
Response = Application.InputBox(Prompt:="Name 1", Type:=2)
If VarType(Response) = vbBoolean Then
    Debug.Print "RunCode2 - User Canceled Name 1 Selection"
    Exit Sub
Else
    If nameExists(Response) = False Then
        MsgBox "Name [" & Response & "] Not Found", vbOKOnly
        Exit Sub
    Else
        Name1 = Response
    End If
End If
Response = Application.InputBox(Prompt:="Name 2", Type:=2)
If VarType(Response) = vbBoolean Then
    Debug.Print "RunCode2 - User Canceled Name 2 Selection"
    Exit Sub
Else
    If nameExists(Response) = False Then
        MsgBox "Name [" & Response & "] Not Found", vbOKOnly
        Exit Sub
    Else
        Name2 = Response
    End If
End If
Main Name1, Name2
End Sub

Upvotes: 1

Related Questions