none
Range to Sequential Numbers in column RRS feed

  • Question

  • Hi ,

    facing an issue in converting a required range into column in sequential manner.

    Range will keep on changing as per the required inputs.

    Eg 

    Range -     Corresponding  value 

    1 - 10           33

    11 -15          35

     16-30          38

    Output 

    1        33

    2       33

    3       33

    4       33

    ...10

    11     35

    12     35

    13    35

    ...15   35

    please note, column cell is constant (Initial value of 1) in excel.

    Thanks

    Monday, August 19, 2019 7:02 AM

All replies

  • Here is a macro:

    Sub Convert()
        Dim wshS As Worksheet
        Dim wshT As Worksheet
        Dim s As Long
        Dim m As Long
        Dim i As Long
        Dim a
        Application.ScreenUpdating = False
        Set wshS = ActiveSheet
        Set wshT = Worksheets.Add(After:=wshS)
        m = wshS.Range("A" & wshS.Rows.Count).End(xlUp).Row
        For s = 2 To m
            a = Split(wshS.Range("A" & s).Value, "-")
            For i = Trim(a(0)) To Trim(a(1))
                wshT.Range("A" & i).Value = i
                wshT.Range("B" & i).Value = wshS.Range("B" & s).Value
            Next i
        Next s
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, August 19, 2019 10:59 AM