none
micro code for converting column into array RRS feed

  • Question

  • In column A have row as follow

    12

    12

    12

    13

    13

    14

    Step1: I have to read value of column A 

    Step2: As soon as I read a value of A1 I want it to store  to the Array and it will goes on till row end

    Step3:Then I have to print above data in another sheet as below

    12_1

    12_2

    12_3

    13_1

    13_2

    14_1

    Please share  code for this 

    Monday, August 21, 2017 5:29 AM

Answers

  • Hi Akshay Chavan 07,

    please refer example below.

    Data in my Sheet.

    Code:

    Option Explicit
    
    Sub demo()
        Dim strArray() As String
        Dim TotalRows As Long
        Dim i As Long
        Dim sht2 As Worksheet
        Set sht2 = Sheets(2)
    Dim t1, t2 As Long
    
     t1 = 0
        TotalRows = Rows(Rows.Count).End(xlUp).Row
        ReDim strArray(1 To TotalRows)
    
        For i = 2 To TotalRows
       
        If (Cells(i - 1, 1).Value = Cells(i, 1).Value) Then
        t1 = t1 + 1
            strArray(i) = Cells(i, 1).Value & "_" & t1
        Else
        t1 = 1
            strArray(i) = Cells(i, 1).Value & "_" & t1
        End If
        Next
       
     For i = 1 To UBound(strArray)
            sht2.Cells(i, 1).Value = strArray(i)
           Debug.Print strArray(i)
        Next
    
    End Sub
    

    Output:

    you can modify the code as per your requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, August 21, 2017 8:39 AM
    Moderator

All replies

  • Hi Akshay Chavan 07,

    please refer example below.

    Data in my Sheet.

    Code:

    Option Explicit
    
    Sub demo()
        Dim strArray() As String
        Dim TotalRows As Long
        Dim i As Long
        Dim sht2 As Worksheet
        Set sht2 = Sheets(2)
    Dim t1, t2 As Long
    
     t1 = 0
        TotalRows = Rows(Rows.Count).End(xlUp).Row
        ReDim strArray(1 To TotalRows)
    
        For i = 2 To TotalRows
       
        If (Cells(i - 1, 1).Value = Cells(i, 1).Value) Then
        t1 = t1 + 1
            strArray(i) = Cells(i, 1).Value & "_" & t1
        Else
        t1 = 1
            strArray(i) = Cells(i, 1).Value & "_" & t1
        End If
        Next
       
     For i = 1 To UBound(strArray)
            sht2.Cells(i, 1).Value = strArray(i)
           Debug.Print strArray(i)
        Next
    
    End Sub
    

    Output:

    you can modify the code as per your requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, August 21, 2017 8:39 AM
    Moderator
  • Thank you so much Deepak

    Wednesday, August 23, 2017 8:31 AM
  • Hi Akshay Chavan 07,

    if your issue is solved then I suggest you to mark the suggestion as an answer which helps you to solve the issue.

    this thread is still open and will remain open until you mark the answer.

    if you have any further question regarding above issue then let us know about that.

    we will try to provide further suggestion to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 23, 2017 8:35 AM
    Moderator