我需要将撕裂范围转换为逗号分隔范围

Question

我的工作表中有一列，每个单元格都有年份范围。（即1995-2005年） 我很了解公式，但我不知道 VBA。 有人可以告诉我一个脚本，我可以用来将此范围更改为单个单元格中逗号分隔的年份范围。 （即1995年，1995年，1997年等）

Answer 1

下面的代码转换为单元格值，如"1995-2000"到"1995,1996,1997,1998,1999,2000"。对于"范围"中指定的所有单元格，这将继续

Sub commaYear()
Dim rng As Range
Dim ws As Worksheet
Dim cellValue As Range
Dim strtYear As Integer
Dim endYear As Integer
Dim tempCelValue As String
Set rng = Range("A1:A10") 'Specify your Coloumn range
For Each cellValue In rng.Cells
    If (cellValue.Value <> "") Then
        strtYear = CInt(Trim(Split(cellValue.Value, "-")(0)))
        endYear = CInt(Trim(Split(cellValue.Value, "-")(1)))
        'tempCelValue = "'"
        While (strtYear <= endYear)
            If (strtYear = endYear) Then
                tempCelValue = tempCelValue & strtYear
                strtYear = strtYear + 1
            Else
                tempCelValue = tempCelValue & strtYear & ","
                strtYear = strtYear + 1
            End If
        Wend
        cellValue.Value = tempCelValue
    End If
Next
End Sub

Answer 2

例如

：http://prntscr.com/9yhns8

=CONCATENATE(A1;",";A2;",";A3;",";A4;",";A5;",";A6;",";A7;",";A8;",";A9;",";A10;",";A11)