根据数组值自动创建Excel工作表 [英] Auto create Excel sheets from Array values

问题描述

我对vba比较陌生。我很早以前就使用过VB，因此我从中获得了很多信息。虽然现在我面临着更艰巨的任务，但我不知道该怎么做。

I am relatively new to vba. I have used VB a long time ago so I derive a lot of info from that experience. Though now I'm facing a harder task and I don't quite know how to do it.

我在E栏软件版本信息中有一个数据表（即 3.1.1， 3.1.2等）。我创建了一个for循环，通过E进行搜索。在这种情况下，有几个类似这样的if语句：

I have got a data sheet with in column E software version information (ie "3.1.1", "3.1.2" and so on). I have created a for loop the searches through E. In this for there are several if statements like this one:

If Cells(r, Columns("E").Column).Value = "3.1.2" Then 'find criteria

            'Copy the current row
            Rows(r).Select
            Selection.Copy

            'Switch to the sprint where you want to paste it & paste
            Sheets("Sprint 2").Select
            Rows(sprint2).Select
            ActiveSheet.Paste

            sprint2 = sprint2 + 1 'next row

            'Switch back to backlog & continue to search for criteria
            Sheets("Backlog").Select
ElseIf...

这对我来说很好，除了我需要在运行宏之前创建工作表之外。我想做的是：

This is working fine for me, except that I need to create the sheets before running the macro. What I would like to do is:

1. 搜索E列


2. 用所有数组填充数组E列中的唯一值* [edit]


3. 为数组中的每个值创建一个工作表

1. Search through column E
2. Fill an array with all unique values in Column E*[edit]
3. Create a sheet for every value in the Array

我很想听听你们的想法。

I would love to hear what you guys think.

推荐答案

也许有帮助：

Sub ColumnE()
Dim colE As Long, r As Long, c As Object, exists As Boolean
Dim values As Collection, i As Long
Set values = New Collection
colE = Columns("E").Column
r = Cells(Rows.Count, colE).End(xlUp).Row
For i = 1 To r ' step 1: loop through column E
    exists = False
    For Each c In values ' step 2: look in collection if the element was already inserted
        If c = Cells(i, colE) Then
            exists = True
            Exit For
        End If
    Next c
    If Not exists Then values.Add Cells(i, colE)
Next i
For Each c In values ' step 3: add a sheet for every value in collection
    Worksheets.Add  ' WARNING: you should test, if there already is a sheet with that name
    ActiveSheet.name = c
Next c
End Sub

我喜欢使用集合而不是数组在vba中，因为我可以动态添加新元素而无需调整大小。 （但这要视情况而定...）

I like to use collections more than arrays in vba, because i can dynamically add new elements without resizing. (but it depends on the situation...)

这篇关于根据数组值自动创建Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！