如何连续连接单元格直到第一个空白单元格 [英] How to concatenate cells in a row until the first blank cell

问题描述

我目前是第一次参加代码/VBA编码.我有一个文件，该文件已转储到当前正在手动组织和推出的工作表中.当放入工作表时，它在单元格之间划界.前2-4个单元格始终是名称的一部分.每当我在给定的一天中收到此转储文件并将其转储到工作表中时，该转储文件的行和列长度将有所不同.例如，一天可能是二十行，一天可能是三十行.

I'm currently diving into code/VBA coding for the first time. I have a file that I dump into a worksheet that currently I'm manually organizing and pushing out. When put into the worksheet, it delimits itself across the cells. The first 2-4 cells are always parts of a name. This dump file will have varying row and column lengths every time I get it in a given day and dump into a work sheet. For example, one day it may be twenty rows and one day it may be thirty.

这是数据外观的粗略说明，但是我的代码可能与下面的示例不匹配-我只是想提供一个视觉效果:

This is a rough illustration of what the data looks like, but my code probably doesn't match with the example below - I just wanted to provide a visual:

因此，我想编写从A1开始并连接其后的单元格的代码，直到它运行到该行的空白单元格中为止.然后，它将串联的数据放入单元格A1中，并删除从中拉出名称块的值，并将所有数据滑动到左侧.之后，它将在下一行继续相同的操作，直到遇到最后一行为止.正如您在图像中看到的那样，我不希望空白单元格之后的任何数据受到影响.

So, I'm wanting to make code that will start at A1 and concatenate the cells following it until it runs into a blank cell in that row. Then it places the concatenated data into cell A1 and removes the values it pulled the name pieces from and slides all the data to the left. After that, it continues the same operation on the next row until it meets the final row. As you can see in the image, I don't want any of the data after the blank cell to be affected.

这通常是我的第一次编程，所以当您提供帮助时，请您解释一下代码，以便我可以学习这些概念吗?这是我认为到目前为止可以使用的方法...我只是停留在如何进行串联上.

This is my first time programming in general, so when you provide assistance, would you please explain your code so I can learn the concepts? Here's what I think will work so far... I'm just stuck on how to go about concatenating.

我当前拥有的代码:

Sub DN_ERROR_ORGANIZER()
  Dim row As Integer
  NumRows = Range("A1", Range("A1").End(xldown)).Rows.Count
  Range("A1").Select
  For row = 1 To NumRows
      Do Until IsEmpty(ActiveCell)
          ' Code to concatenate
          ActiveCell.Offset(1, 0).Select
      Loop
      ActiveCell.Offset(1, 0).Select
  Next
End sub

推荐答案

这是查看问题的另一种方法:假设您的表位于Sheet2上，并且结果反映在Sheet1上.

Here's another way to look at your problem: Suppose you have your table on Sheet2, and the result is reflected on Sheet1.

Sub PutInOrder()
  filledcells = 0
  '''lastrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row

  For i = 1 To 100
     If Sheet2.Cells(i, 1) = "" Then Exit For 
     For a = 1 To 4
        If Sheet2.Cells(i, a) = "" Then Exit For
          If Sheet2.Cells(i, a) <> "" Then
             filledcells = filledcells + 1
        End If
    Next

Select Case filledcells
    Case Is = 2

        Sheet1.Cells(i, 1) = Sheet2.Cells(i, 1) + ", " + Sheet2.Cells(i, 2)
        Sheet1.Cells(i, 3) = Sheet2.Cells(i, 4)
        Sheet1.Cells(i, 4) = Sheet2.Cells(i, 5)
        Sheet1.Cells(i, 5) = Sheet2.Cells(i, 6)
        Sheet1.Cells(i, 6) = Sheet2.Cells(i, 7)


    Case Is = 3

        Sheet1.Cells(i, 1) = Sheet2.Cells(i, 1) + ", " + Sheet2.Cells(i, 2) + " " + Sheet2.Cells(i, 3)
        Sheet1.Cells(i, 3) = Sheet2.Cells(i, 5)
        Sheet1.Cells(i, 4) = Sheet2.Cells(i, 6)
        Sheet1.Cells(i, 5) = Sheet2.Cells(i, 7)
        Sheet1.Cells(i, 6) = Sheet2.Cells(i, 8)


    Case Is = 4

        Sheet1.Cells(i, 1) = Sheet2.Cells(i, 1) + ", " + Sheet2.Cells(i, 2) + " " + Sheet2.Cells(i, 3) + " " + Sheet2.Cells(i, 4)
        Sheet1.Cells(i, 3) = Sheet2.Cells(i, 6)
        Sheet1.Cells(i, 4) = Sheet2.Cells(i, 7)
        Sheet1.Cells(i, 5) = Sheet2.Cells(i, 8)
        Sheet1.Cells(i, 6) = Sheet2.Cells(i, 9)

   End Select
   filledcells = 0
 Next
 End Sub

这篇关于如何连续连接单元格直到第一个空白单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！