VBA | 如何在Excel中将值从单元格复制到单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23668143/
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
VBA | How to Copy value from Cell to Cell in Excel
提问by Nitin Jadhav
I want to copy a cell value to another cell, but I want to retain the value in a variable so I can use it as per requirement.
我想将一个单元格值复制到另一个单元格，但我想将该值保留在一个变量中，以便我可以根据需要使用它。
Following is the code i tried-
以下是我试过的代码-
Private Sub CommandButton1_Click()
NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count
For x = 1 To NumRows
a= Cells(x, 1).Value.Copy
Cells(x, 2).Value= a.PasteSpecial
Next
End Sub
回答by CodeJockey
No need to use the Range.Copymethod. Try this:
无需使用该Range.Copy方法。尝试这个：
Dim a As Variant
a = Cells(x, 1).Value
Cells(x, 2).Value = a
If you want to retain ALL of the values, you will need to use an array:
如果要保留所有值，则需要使用数组：
Dim x As Long
Dim NumRows As Long
NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count
ReDim a(1 to NumRows)
For x = 1 To NumRows
a(x) = Cells(x,1).Value
Cells(X,2).Value = a(x)
Next x
I also recommend explicit variable declaration. It can be found in your options or by typing Option Explicitat the VERY top, above any subs or functions. This will force you to declare your variables. I know it feels like a burden as a newbee, but it only takes one typo getting declared as a new variable to change your mind.
我还推荐显式变量声明。它可以在您的选项中找到，也可以通过Option Explicit在任何子项或功能上方的非常顶部键入来找到。这将迫使您声明变量。我知道作为新手，这感觉像是一种负担，但只需将一个错字声明为新变量即可改变您的想法。
回答by L42
If you just want to pass Range value to array, no need to loop.
Try something like this:
如果您只想将 Range 值传递给数组，则无需循环。
尝试这样的事情：
Dim a
With Sheets("YourSheetName")
a = Application.Transpose(.Range("A1", .Range("A" & _
.Rows.Count).End(xlUp).Address))
End With
'~~> check your array
For i = Lbound(a) To Ubound(a)
Debug.Print a(i)
'~~> rest of your code here to do what you like.
Next
Now, if you want to use your variable aafter code execution, just declare it outside the sub like this:
现在，如果你想a在代码执行后使用你的变量，只需像这样在 sub 之外声明它：
Dim a As Variant '~~> you can also use Public a As Variant
Sub Something()
'~~> put similar code above here and the rest of your code
End Sub
Now, you need to put a test if the variable ais empty or not, else you'll just overwrite it and defeat your purpose.
A simple check like this should work:
现在，您需要测试变量a是否为空，否则您只会覆盖它并破坏您的目的。
像这样的简单检查应该可以工作：
If IsEmpty(a) Then '~~> or IsArray if you're sure you'll always have a as array
'~~> populate a
'~~> proceed with what you want to do with a
Else
'~~> proceed with what you want to do with a
End If
Hope it helps.
希望能帮助到你。
回答by user3516774
First, I suggest that you use the Record Macro facility on the Developer ribbon. Be sure to set "Use Relative References" on, and record the mouse clicks and keystrokes of one iteration of what you want to do (copy A1 to B1). Then open the macro in VB and modify it.
首先，我建议您使用 Developer 功能区上的 Record Macro 工具。确保将“使用相对引用”设置为开启，并记录您想要执行的一次迭代的鼠标点击和击键（将 A1 复制到 B1）。然后在VB中打开宏并修改。
This is what I got when I used this approach, and it seems to work for me. I hope it works for you too.
这就是我使用这种方法时得到的结果，它似乎对我有用。我希望它也适用于你。
Sub Macro1()
Dim NumRows As Integer
NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count
Range("A1").Activate
For i = 1 To NumRows
ActiveCell.Select
Selection.Copy
ActiveCell.Offset(0, 1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(1, -1).Activate
Next
End Sub
