如何将一个工作表中的单元格复制到另一个工作表中，到正确的行 [英] how to copy a cell from one worksheet to another, to the correct row

问题描述

我有一个csv文件(它是打印机的导出文件)，可读性很差，并且还包含很多不需要的信息.所以我写了一个VBA宏，将相关数据复制到第二张纸上.

I have a csv file (which is an export from a printer), which is not nicely readable and it also has a lot of information which is not needed. So I had written a VBA macro to copy the relevant data to a second sheet.

使用vba代码，我正在工作表1中查找要复制到工作表2中的特定文本短语.这些文本短语不在工作表1的特定列中，因此我必须仔细阅读整个工作表1.我的代码正在运行，但是我有一个问题对我来说很棘手，这就是为什么我需要您的帮助.

With the vba code, I'm looking in worksheet 1 for specific text phrases which will be copied to worksheet 2. These text phrases are not in a specific column in worksheet 1, so I have to look through the complete sheet 1. My code is working, but I have one problem which is to tricky for me and that's why I need your help.

在我的情况下，我正在寻找短语"Druckbeginn ="(这是德语，表示Printstart)，后跟一个unix时间码.在大多数行中，短语"Druckbeginn =存在，但在某些行中则不存在.

In my case, I'm looking for the phrase "Druckbeginn =" (this is German and means Printstart) and is followed by a unix timecode. In most rows, the phrase "Druckbeginn =" exists, but in some it does not.

因此，让我们在第1、2和4行说该短语存在，但在第3行则不存在.问题是，我的vba代码将单元格从工作表1复制到工作表2，但是它复制到行1，2和3而不是行1、2和4.

So lets say in row 1, 2 and 4 the phrase is existing but in row 3 not. The problem is, that my vba code copies the cells from worksheet one to worksheet two, but it copies into the row 1 ,2 and 3 and not to 1, 2 and 4.

如果您需要我的csv文件和/或完整的vba代码，请告诉我.

If you need my csv file and/or my complete vba code, please let me know.

这是我的代码:

w1.Activate
For Each r In Intersect(Range("A:AS"), ActiveSheet.UsedRange)
   v = r.Value
   If InStr(v, "Druckbeginn =") > 0 Then
      r.Copy w2.Cells(B, 3)
      B = B + 1
   End If
Next r

推荐答案

解决方案:该代码对我有用:

' Insert variable declarations etc.
Set w1 = ThisWorkbook.Sheets("Sheet1")
Set w2 = ThisWorkbook.Sheets("Sheet2")
For Each r In Intersect(w1.Range("A:AS"), w1.UsedRange)
    If InStr(r.Value, "Druckbeginn =") > 0 Then
        ' The important change is in the next line
        w2.Cells(r.Row, 3).Value = r.Value
    End If
Next r

结果 :(在获取示例数据之前，看起来更加简单)

Result: (before obtaining the sample data so looking a bit more simple)

工作表1:

第2张(运行宏后):

解决方案的说明:在您的代码中，当找到字符串导致所有复制的单元格都复制到页面顶部时，您只会增加 B .结果表中的第一个列，而不是源数据中的相应行.相反，我们可以简单地引用源数据的 .Row 属性. r.Row 是 For Each 循环中当前 r 元素的行号，因为您希望该单元格位于同一行中 ws2 中的数字，您可以使用相同的行号.

Explanation of the solution: In your code, your are only increasing B when the string is found resulting in all the copied cells to be copied to the top of the column in your results sheet instead of to the corresponding line in the source data. Instead, we can simply refer to the .Row attribute of the source data. r.Row is the row number of the current r element of your For Each loop and since you want the cell to be in the same row number in ws2, you can use the same row number.

代码改进说明:

(1)对于相交的每个r(w1.Range("A:AS")，w1.UsedRange):为避免混淆 .Range 是指，我添加了 w1.说明符.

(1) For Each r In Intersect(w1.Range("A:AS"), w1.UsedRange): To avoid confusion about which worksheet the .Range is referring to, I added the w1. specifier.

(2)如果InStr(r.Value，"Druckbeginn =")>0然后:您可以直接在 If 子句中使用 r.Value ，而不是分配 v = r.Value 以后不再使用.

(2) If InStr(r.Value, "Druckbeginn =") > 0 Then: Instead of assigning v = r.Value you can just use r.Value in your If clause directly since you are not using it again later.

(3) w2.Cells(r.Row，3).Value = r.Value :复制和粘贴操作需要在工作表之间跳转，这在性能方面比直接分配值更糟通过其 .Value 属性访问单元格，尤其是在处理大型文件时.

(3) w2.Cells(r.Row, 3).Value = r.Value: Copy and paste actions require jumping between worksheets which is worse in terms of performance than directly assigning a value to a cell through its .Value attribute, especially when you are going through large files.

这篇关于如何将一个工作表中的单元格复制到另一个工作表中，到正确的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！