使用逻辑从另一张纸上的一列在一张纸上创建多列

发布于 2024-09-24 01:38:45 字数 1114 浏览 5 评论 0原文

我是 VBA 新手，我正在努力使这项工作顺利进行。我需要一个宏来处理 Sheet1 上的每个单元格\列，并将结果放在 Sheet2 上。我确信这对于那些更熟悉 VB 代码的人来说是相当容易的。它包含许多列。

每当我们遇到 — 或空单元格时，我们都会用 -999 填充该单元格（请参阅 Sheet2 上的示例））在第一列，其中包含“0-2”。然后，我们创建 2 个新列，并在第一列上填充 0，在第二列上填充 2（请参阅 Sheet2 上的示例）。如果仅在一侧找到值，我们就会用相同的数字填充两侧。

中处理

由于每列都在 Sheet1 和Sheet1

A               B
Column1         Column2
Title           Title2
0–2             0–4
3               —
—               5
—               
—               —
10–23           11—29

上应如下所示

，结果在 Sheet2 Sheet2

A          B            C            D
Column1    Column1      Column2      Column2   
Title-A    Title-B      Title-A      Title-B
0          2            0            4
3          3            -999         -999
-999       -999         5            5
-999       -999         -999        -999
—999       -999         -999        -999
10         23           11           29

原文

I am new to VBA and I am struggling trying to make this work. I am in need of a macro that will process each cell\column on Sheet1 and put the results on Sheet2. I'm sure this is pretty easy for those who are more advanced with VB code. It contains many columns..

Anytime we encounter a — or an empty cell, we populate the cell with -999 (see the example on Sheet2) on the first column, which contains "0-2". Then we create 2 new columns and populate them with 0 on the first column and 2 on second column (see example on Sheet2). If a value is found on only one side, we then populate both sides with the same number.

As each column is being process in Sheet1 and

Sheet1:

A               B
Column1         Column2
Title           Title2
0–2             0–4
3               —
—               5
—               
—               —
10–23           11—29

And the results should look like this on Sheet2

Sheet2

A          B            C            D
Column1    Column1      Column2      Column2   
Title-A    Title-B      Title-A      Title-B
0          2            0            4
3          3            -999         -999
-999       -999         5            5
-999       -999         -999        -999
—999       -999         -999        -999
10         23           11           29

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

﹏半生如梦愿梦如真 2024-10-01 01:38:45

您实际上并不需要 VBA 来执行此操作。这一切都可以使用 if 函数来完成。

第 1 列将使用类似的内容：

=IF(ISNUMBER(Sheet1!A2),Sheet1!A2,IF(OR(Sheet1!A2="-",ISBLANK(Sheet1!A2)),-999,LEFT(Sheet1!A2,1)))

第 2 列将使用类似的内容：

=IF(ISNUMBER(Sheet1!A2),Sheet1!A2,IF(OR(Sheet1!A2="-",ISBLANK(Sheet1!A2)),-999,RIGHT(Sheet1!A2,1)))

3 和 4 将使用与 1 和 2 相同的内容，只是在列上移动。

You don't really need VBA to do this. It can all be done using the if function.

Column 1 would use something like:

=IF(ISNUMBER(Sheet1!A2),Sheet1!A2,IF(OR(Sheet1!A2="-",ISBLANK(Sheet1!A2)),-999,LEFT(Sheet1!A2,1)))

Column 2 would use something like:

=IF(ISNUMBER(Sheet1!A2),Sheet1!A2,IF(OR(Sheet1!A2="-",ISBLANK(Sheet1!A2)),-999,RIGHT(Sheet1!A2,1)))

3 and 4 would use the same as 1 and 2 just shifted over a column.

回复收藏 0 原文

望她远 2024-10-01 01:38:45

好吧，你的任务基本上是由多个部分组成的：
1 - 使用智能逻辑遍历工作表
2 - 分裂
3 - 将数据放入新单元格

这并没有证明可以工作，但这应该为您提供弹药，以较小的努力实现您的目标。

Sub mySub()
  dim i,j,dummy,dummy2, blankCounter
  i=1
  j=1
  blankCounter=0
  '@@@@@@Section1
  do while ThisWorkbook.Worksheets(1).Cells(1, j)<>""
  do while blankCounter<=5
        if ThisWorkbook.Worksheets(1).Cells(i, j)=""
          blankCounter=blankCounter+1
        else
        '@@@@@@Section2
       dummy=Split(ThisWorkbook.Worksheets(1).Cells(i, j),"—")
       for k=0 to Ubound(dummy)
        ThisWorkbook.Worksheets(2).Cells(i, j+k)=dummy(k)
       next k
        end if
    i=i+1
  loop
                    blankCounter=0
        j=j+1
  loop


end Sub

好的，用英语。第一节是查看本工作簿（“this”经常出现，尤其是在 java 中，但这是这种逻辑的一个更明显的例子）。在本工作簿中查看我的第一个工作表。在此工作表中，让我们看看 col1、row1。现在我们将循环行并继续直到它们为空，对列执行相同的操作。这基本上会找到矩阵（数据）的维度。

好的，现在我们可以遍历工作簿了，我们已经成功了一半。

现在您将了解 VBA 中的拆分功能，该功能也存在于几乎所有语言中。 Split 在字符串中查找某些内容，并根据您提供的参数分解字符串。 Dummy 将保留任何返回值。虚拟的大小是未知的，所以我们不应该对此进行硬编码，否则当分离器不存在时它会中断。

话虽这么说，我们现在介绍一下Ubound。 Ubound 是一种查找数组大小的功能。请注意这一点：如果 Ubound 返回 1，则意味着您有一个值。数组自动索引为 0，因此您必须注意这一点，不要从 1 循环到 Ubound，这会产生错误并产生不正确的代码。

好的，现在我们可以遍历数组和工作簿了，现在我们只需要将这些值放入所需的单元格中即可。 var k 应该能够为你做到这一点。

正如我所说，我尚未验证此代码，因此您需要确保此逻辑正常运行，因为我现在意识到它并不会将数据放置在正确的单元格中，但这将由您来解决。

Okay, your task is basically multi-part:
1 - Traversing a sheet with smart logic
2 - Splitting
3 - Placing data in new cell

This isn't proofed for working but this should give you the ammunition to accomplish your goal with minor effort.

Sub mySub()
  dim i,j,dummy,dummy2, blankCounter
  i=1
  j=1
  blankCounter=0
  '@@@@@@Section1
  do while ThisWorkbook.Worksheets(1).Cells(1, j)<>""
  do while blankCounter<=5
        if ThisWorkbook.Worksheets(1).Cells(i, j)=""
          blankCounter=blankCounter+1
        else
        '@@@@@@Section2
       dummy=Split(ThisWorkbook.Worksheets(1).Cells(i, j),"—")
       for k=0 to Ubound(dummy)
        ThisWorkbook.Worksheets(2).Cells(i, j+k)=dummy(k)
       next k
        end if
    i=i+1
  loop
                    blankCounter=0
        j=j+1
  loop


end Sub

okay, in english. Section one is looking at this workbook ("this" appearing commonly especially in java but this is a more palpable example of such logic). In this workbook look at my first worksheet. In this worksheet lets look at col1,row1. Now we are going to loop down the rows and keep going until they are blank, do the same with the columns. This will basically find the dimensions of your matrix (data).

Okay, so now that we can traverse the workbook we are half way there.

Now you get to know the Split functionality in VBA that also exists in almost every language. Split looks for something in your String and breaks up your string by the argument you supply. Dummy will hold whatever the return values are. The size of dummy is unknown, so we should not hardcode this otherwise it will break when the splitter is not present.

That being said, we now introduce Ubound. Ubound is a functionality that finds the size of your array. Note on this: if Ubound returns 1, this means you have one value. Arrays are auto indexed to 0 so you must note this and not loop from 1 to Ubound, this will give an error and also produce incorrect code.

Okay, good now that we can traverse our array and our workbook now we just need to put these values in the cells desired. var k should be able to do this for you.

As i said i haven't proofed this code, so you are going to need to make sure that this logic functions properly, as i realize now that it does not wrt placing the data in the correct cell, but this will be yours to solve.

回复收藏 0 原文

~没有更多了~