如何从起始编号和结束编号创建一个列表 [英] How to create a list from beginning number and end number

问题描述

我有一组数字：

  A B 
 -------------- 
 1 | 100 102 
 2 | 103 103 
 3 | 104 105 
 4 |列表A是起始号，列B是结尾号。我们需要使用列A& A的开头号和结束编号创建一个列表（在单独的单元格上）。 B. 
例如根据第1行（A1& B1）的第1组数据，第1组数字将为：100,101,102，然后进入第2行，102后放置103，移至第3行，展开列表并显示104和105，然后到最后一行应该列出105,106,107,108,109,110。
 
 
 
我们应该能够标记列表的开头，以便我们知道每个列表。也就是说，列A中列出的所有数字都应该被标记。
解决方案
不知道我完全理解你的问题，要这样做：
 
 100 102 
 103 103 
 104 105 
 106 110 
 
到这个？
 
 100 102 100，101，102 
 103 103 103 
 104 105 104,105 
 106 110 106,107,108,109,110 
 
如果是这样，以下代码将实现：
 
 Private Sub getListsOfNumbers（）
 Dim inputRange As String 
 Dim x As Long 
 Dim y As Long 
 
'获取输入数据范围
 inputRange = InputBox（输入范围，开始，A1：A4 ）
 
'清除输出范围（两列偏移量）
范围（inputRange）.Offset（0,2）.ClearContents 
 
带范围（inputRange）
 
'循环输入范围
对于x = 1到.Cells.Cou nt 
 
'循环通过第二列和第一列之间的差异
对于y = 0 To（.Cells（x，2） -  .Cells（x，1））
 
'向输出列添加值
 .Cells（x，3）= .Cells（x，3）&（.Cells（x，1）+ y）&，
 Next y 
 
'通过删除引导逗号来整理输出
 .Cells（x，3）= CStr（Left（.Cells（x，3），Len（.Cells（x，3）） -  2））
下一个x 
结束
 End Sub 
 
如果我误读了请求，请让我知道。
 
 
 
编辑：只是尝试了这个真实的，与更大的数据集，这将是一个缓慢的人们可能预测。如果您的数据具有100/1000行，和/或A和A列中的数字之间的差异。 B明显大于示例，那么您可能希望通过在程序开始时关闭计算和screenUpdating来查看最小化延迟。完成后还原。
 
 
 
 Excel帮助有语法和示例来帮助您，如果您需要实现这一点。
 
I have a set of numbers:
 |  A     B
--------------
1| 100   102
2| 103   103
3| 104   105
4| 106   110
Column A is the beginning number and Column B is the end number. We need to create a list (on a separate cell) of numbers using the beginning number and end number using column A & B. 
E.g. based on the 1st set of data from Row 1(A1 & B1) the 1st set of numbers will be: 100,101,102, then it will go to row 2, put 103 after 102 and move on to row 3, expanded the list and display 104 and 105, then to last row where it should list 105,106,107,108,109,110.

We should be able to mark the beginning of the number for the list so that we know the start of each list. i.e. all the number listed in Column A should be marked.
解决方案
Not sure I completely understand your question, but I think you want to turn this:
100 102
103 103
104 105
106 110
into this?
100 102     100, 101, 102
103 103     103
104 105     104, 105
106 110     106, 107, 108, 109, 110
If so, the following code will achieve this:
Private Sub getListsOfNumbers()
    Dim inputRange As String
    Dim x As Long
    Dim y As Long

    'Get input range of data
    inputRange = InputBox("Enter input range", "Start", "A1:A4")

    'Clear output range (two column offset)
    Range(inputRange).Offset(0, 2).ClearContents

    With Range(inputRange)

        'Loop through input range
        For x = 1 To .Cells.Count

            'Loop through difference between second column and first column
            For y = 0 To (.Cells(x, 2) - .Cells(x, 1))

                'Add value to output column
                .Cells(x, 3) = .Cells(x, 3) & (.Cells(x, 1) + y) & ", "
            Next y

            'Tidy up output by removing trailling comma
            .Cells(x, 3) = CStr(Left(.Cells(x, 3), Len(.Cells(x, 3)) - 2))
        Next x
    End With
End Sub
If I've misread your request, please let me know.

Edit: Just tried this for real and, with larger datasets, it would be as slow as one might predict.  If your data has 100s/1000s of rows, and/or the difference between the numbers in columns A & B is significantly larger than the example, then you'd probably want to look at minimising the delay by turning off calculation and screenUpdating at the beginning of the procedure and restoring once complete.

The Excel help has the syntax and examples to help you if you need to implement this.

                        
这篇关于如何从起始编号和结束编号创建一个列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！