使用 VBA 动态设置范围等于另一个范围

Set range equal to another range dynamically using VBA

提问by kirk

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

我想参考一个命名范围来设置一个单元格范围，该范围name1等于另一个 Excel 工作表上同样大小的单元格范围，也引用了一个命名范围name2。我希望单元格Sheet1等于Sheet2当前等于的相应单元格，因此我不能使用 .value 属性。

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 eachand R1C1naming? To reiterate - the cells on sheet1should equal whatever the value is of the relative cell on sheet2(ranges are the same size). So, eg. cell Sheet1!A1has the formula =Sheet2!A1.

除了，我不想使用价值。有没有像这样的简单方法来完成我的要求？我搜索了一些论坛，但找不到一个好的方法来做到这一点。我需要使用 aFor each和R1C1命名吗？重申 - 上的单元格sheet1应该等于相对单元格上的任何值sheet2（范围大小相同）。所以，例如。单元格Sheet1!A1有公式=Sheet2!A1。

采纳答案by Joe

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:

如果范围（name1 和 name2）位于不同的位置，则需要相应地调整公式：

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

回答by NickSlash

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

我只测试了一点，所以它可能不是那么健壮。

NoteThis 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.

它是一个宏，因此不能作为 UDF 从工作表中调用。此外，由于它有参数，因此不能直接调用。

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 RunCodeshould be available in the macros menu on in your workbook.

subRunCode应该在您的工作簿的宏菜单中可用。

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 RunCode2macro will prompt you to enter the two names to pass to Main

如果您希望该函数的界面更可定制，那么以下代码应该会有所帮助。运行RunCode2宏将提示您输入要传递给的两个名称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