Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Convert Series into Range.

  1. #1
    Member ayazgreat's Avatar
    Join Date
    Mar 2012
    Posts
    86
    Rep Power
    13

    Convert Series into Range.

    Dear Krishnakumar

    I require your help here again regarding to mentioned below link

    Convert different series into its Start and End Range - Page 3 - MrExcel Message Board

    Thanks in Advance

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi Ayaz,

    Welcome to ExcelFox !!

    try

    Code:
    Sub kTest()
        
        Dim ka, k(), i As Long, n As Long, c As Long
        
        ka = Range("a1").CurrentRegion.Resize(, 2)
        
        ReDim k(1 To UBound(ka, 1), 1 To 3)
        
        For i = 2 To UBound(ka, 1)
            If i = 2 Then
                n = n + 1: c = c + 1
                k(n, 1) = ka(i, 1)
                k(n, 2) = ka(i, 1)
                k(n, 3) = c
            Else
                If ka(i, 2) - ka(i - 1, 2) = 1 Then
                    c = c + 1
                    k(n, 2) = ka(i, 1)
                    k(n, 3) = c
                Else
                    n = n + 1: c = 1
                    k(n, 1) = ka(i, 1)
                    k(n, 2) = ka(i, 1)
                    k(n, 3) = c
                End If
            End If
        Next
        If n Then
            Range("d1:f1") = Array("Start", "End", "Qty Count")
            Range("d2").Resize(n, 3) = k
        End If
        
    End Sub
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Member ayazgreat's Avatar
    Join Date
    Mar 2012
    Posts
    86
    Rep Power
    13
    Thank you very much for your reply, I have just tried your code and found an issue regarding to a single number if a series has only one number with MB number then code does not convert it into range.

    And one thqing more is that if you please once again see my mentioned link result it is from col d to col j in last updated post.

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Upload a sample workbook with actual layout.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Member ayazgreat's Avatar
    Join Date
    Mar 2012
    Posts
    86
    Rep Power
    13
    I am not able attach Excel file here from mobile but I am attaching a pic view if you could see
    Attached Images Attached Images

  6. #6
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Try

    Code:
    Sub kTest()
        
        Dim ka, k(), i As Long, n As Long, c As Long
        
        ka = Range("a1").CurrentRegion.Resize(, 2)
        
        ReDim k(1 To UBound(ka, 1), 1 To 7)
        
        For i = 2 To UBound(ka, 1)
            If i = 2 Then
                n = n + 1: c = c + 1
                k(n, 1) = ka(i, 1): k(n, 2) = ka(i, 1): k(n, 3) = c
                k(n, 5) = ka(i, 2): k(n, 6) = ka(i, 2): k(n, 7) = c
            Else
                If ka(i, 2) - ka(i - 1, 2) = 1 Then
                    c = c + 1: k(n, 2) = ka(i, 1): k(n, 3) = c
                    k(n, 6) = ka(i, 2): k(n, 7) = c
                Else
                    n = n + 1: c = 1
                    k(n, 1) = ka(i, 1): k(n, 2) = ka(i, 1): k(n, 3) = c
                    k(n, 5) = ka(i, 2): k(n, 6) = ka(i, 2): k(n, 7) = c
                End If
            End If
        Next
        If n Then
            Range("d1:j1") = Array("Start", "End", "Qty Count", "", "MCP Start", "MCP End", "MCP Count")
            Range("d2").Resize(n, 7) = k
        End If
        
    End Sub
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  7. #7
    Member ayazgreat's Avatar
    Join Date
    Mar 2012
    Posts
    86
    Rep Power
    13
    Kris there is a problem with a single number in seruies if you see in attached pic there is single 30735609 with MB number 9002 , after running. Code the Qty or count result is 3 , however it is should be 1

  8. #8
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    I got the same results what you shown in the picture. So what's no working for you ?

    1.JPG
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  9. #9
    Member ayazgreat's Avatar
    Join Date
    Mar 2012
    Posts
    86
    Rep Power
    13
    Kris I mean to say if I say there are different numbers in series with their MB number in col b if there is a single number in series like 307392589 then result should be 307392589 to 307392589 equal 1 in Qty or count col but result in your code is equal to 3 not 1

  10. #10
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    I'm not getting you. Can you upload a workbook with expected results ?
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Replies: 4
    Last Post: 06-10-2013, 01:27 PM
  2. Subtraction Of Series Of Cells' / Array Values
    By PcMax in forum Excel Help
    Replies: 6
    Last Post: 10-26-2012, 11:55 PM
  3. Graphing a bell curve from an Excel data series
    By larryt1940 in forum Excel Help
    Replies: 2
    Last Post: 01-24-2012, 06:08 AM
  4. Color Chart Series VBA
    By Admin in forum Download Center
    Replies: 0
    Last Post: 04-29-2011, 11:26 PM
  5. Replies: 4
    Last Post: 04-07-2011, 07:09 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •