Products
Services
Contact Us

Scripts: asp :: Excel Macros (VB.NET) :: Library Article #19

Developer's Section

Creating an Excel Macro to Redistribute Data
By: Erobo Software

Hire a Developer for Related Work / Installation | $55 hr
Rating:  | Rate It:   
Average Votes: (718)
Favorites:

In this tutorial we create an excel macro that is going to take two columns and redistribute the contents of them using a macro that reads them.

In the code below we create a macro that reads Sheet1 and outputs Sheet2 with the items rearranged in a way that the items in Column 1 are Paired with each of the items in Column 2.

Original Data

1A,B,C,D
2X,F

New Data After Macro Applied

1A
1B
1C
1D
2X
2F

Code:

 Code Snippet 1

Sub CreateDataDistribution
  Call CreateDataDistribution("Sheet1","Sheet2")
End Sub

Sub createDataDistribution(shtBefore as String, shtAfter as String)

Dim mycell as Range
Dim mydiffs as Integer
Dim currSer as String
Dim compSer as String
Dim currSer2 as String
Dim compSer2 as String
Dim outputI as Integer
Dim identityInsert as Integer
Dim FoundV as Boolean
Dim FoundV2 as Boolean

outputI = 1
identityInsert = 1

For Each mycell in ActiveWorkbook.Worksheets(shtBefore).UsedRange

  If mycell.Column = 2 Then
    currSer = ActiveWorkbook.Worksheets(shtBefore).Cells(mycell.Row,2).Value
    
    If Trim(currSer) <> "" Then

      Dim headends() as String
      headends = Split(Trim(currSer), ",")

      Dim i as Integer

      for i = 0 to UBound(headends)

        If Trim(headends(i)) <> "" Then

          ActiveWorkbook.Worksheets(shtAfter).Cells(outputI,1).Value = ActiveWorkbook.Worksheets(shtBefore).Cells(mycell.Row,1).Value
          ActiveWorkbook.Worksheets(shtAfter).Cells(outputI,2).Value = Trim(headends(i))
          
          outputI = outputI + 1

        End If
      Next
    End If

  End If

End Sub


See other Scripts in Excel Macros (VB.NET)

Submit Your Scripts:

If you would like to have your ASP & ASP.NET scripts published in this section please fill out
the form below:
*Your Name or Username:
Home Town:
*Email:
*Description and Code:
*Enter Code shown
to the right:

[ Refresh Image ]