Thursday, April 19, 2012


Transposition in Microsoft Excel

Within Cell A1, you can see all the data lumped into A1. 
[A1] [aaa, bbb, ccc, ddd]

My objective is to get
[A1] [aaa]
[A2] [bbb]
[A3] [ccc]
[A4] [ddd]

The following code as below.

Sub Transpose()
‘Andy’s Transpose Coding version 1.0 @ April 18 2012

'Declare all the necessary variables as Variant because tags are alphanumeric and size is difficult to determine.
Dim X, Y, Z As Variant
A Input box to ask the user what is the active cell number. E.G If your active cell is in A90, then enter “90” in the prompt.
Z = InputBox("What is the active cell number to transpose?")
'count the number of tags separated by a comma in each cell
Y = Len(Range("E" & Z & "")) - Len(Application.WorksheetFunction.Substitute(Range("E" & Z & ""), ",", ""))
A message box to confirm how many tags are found that cell.
MsgBox Y
'Add rows after finding out how many in the first cell.
Dim i As Integer
For i = 1 To Y
Next i
A message box to confirm the rows are created.
MsgBox "Rows Created"
'Transposing the data(tags) into new rows
X = Split(Range("E" & Z & "").Value, ",")
Range("E" & Z & "").Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X)

End Sub

Wednesday, May 18, 2011

Welcome to my blog

Welcome to my blog codename "Neural Firings" where I will dedicate it to stuffs like database administration, database development, scripting, OLAP, Automation. Process Improvements using ITIL, Metrics, KPI, Reporting Tools. My interests is revolved around business analysis, statistical tools, metrics and reporting, managing KPIs, data analytics, business intelligence tools.