将行的单元格从一列复制/粘贴到另一列 - VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23563436/
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
copy/paste rows′ cell from a column to another one - VBA
提问by Andrea
I have some data on a spreadsheet. In particular i need the TIME that is in the first column and in the second one depending on the time, for strange reasons when i import the txt file into excel if the time is from 10 AM to 23.59 the time is on the column A otherwise time in in B. I′m trying to make a VBA script that could allow me to put all the data in the same column (A). My idea is : - for all the rows in the colum A - if the cell is empty copy the value of the B cell (same row) in the actual cell (ex. if A1 is empty then copy B1 in A1) - Stop
我在电子表格上有一些数据。特别是我需要根据时间在第一列和第二列中的时间，出于奇怪的原因，当我将 txt 文件导入 excel 时，如果时间是从上午 10 点到 23.59 时间在 A 列上，否则在 B 中的时间。我正在尝试制作一个 VBA 脚本，它可以让我将所有数据放在同一列 (A) 中。我的想法是： - 对于列 A 中的所有行 - 如果单元格为空，则在实际单元格中复制 B 单元格（同一行）的值（例如，如果 A1 为空，则在 A1 中复制 B1） - 停止
Sub Test()
子测试()
Dim i As Integer
Dim lastRow As Long
Dim x, y As Integer
Dim rng As String
Sheets("Sheet1").Select
' count numer of recorsd (in the first column) - check the range
lastRow = Worksheets("Sheet1").Range("B:B").Cells.SpecialCells(xlCellTypeConstants).Count
For i = 1 To lastRow
If IsEmpty(Worksheets("Sheet1").Range(Cells(1, i))) Then Range(Cells(2, i)).Select: Range (Cells (1, i)).Copy: Range(Cells(1, i)).PasteSpecial: Range(Cells(2, i)).Clear
Next i
End Sub
结束子
I tryed to do somethings but it shows up an error:
′run-time error ′1004′:
Application-defined or object-defined error
我试图做一些事情，但它显示了一个错误：'运行时错误'1004'：
应用程序定义或对象定义的错误
Any ideas?
有任何想法吗？
回答by Adach1979
I see a couple of things with your code. First, the variable lastrowis counting the number of items in Column B, does that does not necessary mean that it will give you the last row number so I suggested a change in my code below to fix this. Also, your range references for Cells have the column/row swapped, I fixed that as well in the code below. Finally, the reason why you are receiving the error is because your reference to the cell is incorrect. Here is my recommendation and should do what you are looking for
我看到你的代码有几件事。首先，该变量lastrow正在计算 B 列中的项目数，这是否意味着它会为您提供最后一行编号，因此我建议更改下面的代码以解决此问题。此外，您对单元格的范围引用交换了列/行，我也在下面的代码中修复了这个问题。最后，您收到错误的原因是您对单元格的引用不正确。这是我的建议，应该做你正在寻找的
Sub Main()
Dim i As Integer
Dim lastRow As Long
Dim x As Integer
Dim y As Integer
Dim rng As Range
Sheets("Sheet1").Activate
' count numer of recorsd (in the first column) - check the range
With ActiveSheet
lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
For i = 1 To lastRow
Set rng = Cells(i, 1)
If IsEmpty(rng) Then
Cells(i, 2).Copy
Cells(i, 1).PasteSpecial
Cells(i, 2).Clear
End If
Next i
End Sub
回答by EngJon
The method IsEmptyis not referring to Cells in the sheet, but to uninitialized variables. You can simply check whether a cell contains an empty string. Hope the following code helps:
该方法IsEmpty不是指工作表中的单元格，而是指未初始化的变量。您可以简单地检查单元格是否包含空字符串。希望以下代码有帮助：
Sub Copy()
Dim i As Integer
Dim lastRow As Long
lastRow = ActiveSheet.UsedRange.Rows.Count
For i = 1 To lastRow
If (Cells(i, 1) = "") Then
Cells(i, 1) = Cells(i, 2)
Cells(i, 2).Clear
End If
Next i
End Sub
It checks the cells in column A for empty strings and copies the value of the same row cell in column b, which will be deleted afterwards.
它检查 A 列中的单元格是否为空字符串，并复制 b 列中同一行单元格的值，之后将删除该值。
