Excel VBA 宏：通过引用复制单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14884381/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA Macro: Copying cells by reference
提问by Barret Brown
I am creating a macro in Excel to copy data from one sheet to another within the same workbook. Currently, the code operates as so:
我正在 Excel 中创建一个宏，以将数据从一张工作表复制到同一工作簿中的另一张工作表。目前，代码运行如下：
Dim sourceSheet As Worksheet
Dim destSheet As Worksheet
Dim numCases As Integer
Dim i As Integer
numCases = 10
Set destSheet = ThisWorkbook.Sheets("Raw_Data")
Set sourceSheet = ThisWorkbook.Sheets("Formatted_Data")
For i = 1 To numCases
destSheet.Cells(i,1).Value = sourceSheet.Cells(1,i).Value
Next
This code works properly in that it copies the value from one sheet to the other and places it in the correct cell. The issue is that I need the cell to be populated as a reference value back to sheet "Raw_Data". In other words, when I select a cell in "Formatted_Data", I need the formula bar to show something like: 'Raw_Data'!C1 but instead all I get is the numerical value. Is there some way that I can perform a copy, or set a value and maintain the reference??
此代码工作正常，因为它将值从一张纸复制到另一张纸并将其放置在正确的单元格中。问题是我需要将单元格作为参考值填充回工作表“Raw_Data”。换句话说，当我在“Formatted_Data”中选择一个单元格时，我需要公式栏显示如下内容：'Raw_Data'!C1 但我得到的只是数值。有什么方法可以执行复制，或设置值并维护引用？
Thanks,
谢谢，
Barret
巴雷特
回答by Scott Holtzman
Replace
代替
destSheet.Cells(i,1).Value = sourceSheet.Cells(1,i).Value
destSheet.Cells(i,1).Value = sourceSheet.Cells(1,i).Value
With
和
sourceSheet.Cells(1,i).Formula = "=" & destSheet.Name & "!" & destSheet.Cells(i,1).Address(False,False)
sourceSheet.Cells(1,i).Formula = "=" & destSheet.Name & "!" & destSheet.Cells(i,1).Address(False,False)
