Saturday, January 5, 2013


As expected, no one commented my last post. Never mind, I'm posting my solution anyway.

Be warned though, as a non-programmer who only started using Excel VBA less than a month ago, my codes are not really efficient.

Nevertheless, the trainer for VBA fundamental offer this advice before our session ended;
"As long as your code is working, who cares whether it is efficient or not? You are not a programmer. Even if your code is super efficient, PC nowadays are so fast the different will only be like a few seconds... or milliseconds. What important is you understand your codes and able to use it"
For those of you who never use VBA before, here's a quick guide to start:
1. Open MS Excel, press alt + F11 to open the VB Editor.
2. At the menu bar, Insert > Module. You write your codes in here.

Here's how I did it:

1. Use VBA Split function to create an array from the cell with the equipment list.

Sub BreakItUp()

Dim eqListCombined As String
Dim eqList As Variant
Dim eqListNo As Integer

eqListCombined = ActiveCell.Value

eqList = Split(eqListCombined, Chr(10))
For eqListNo = LBound(eqList) To UBound(eqList)
   ActiveCell.Offset(1, 0).Select
   ActiveCell.Value = eqList(eqListNo)
Next eqListNo

2. Copy down everything on the left side of the initial cell.

Dim i As Integer
Dim KiraColumn As Integer

KiraColumn = ActiveCell.Column
ActiveCell.Offset(0 - eqListNo, 1 - KiraColumn).Resize(1, KiraColumn - 1).Copy
ActiveCell.Offset(0 - eqListNo, 1 - KiraColumn).Select
For i = 1 To eqListNo
    ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues
Next i
Application.CutCopyMode = False

3. Copy down everything on the right side of the initial cell.

Dim KiraRange As Integer
Dim j As Integer

ActiveCell.Offset(0 - eqListNo, KiraColumn).Select
KiraRange = ActiveCell.CurrentRegion.Columns.Count - ActiveCell.Column + 1
ActiveCell.Resize(1, KiraRange).Copy
For j = 1 To eqListNo
    ActiveCell.Offset(1, 0).PasteSpecial xlPasteValues
Next j
Application.CutCopyMode = False

4. Calculate the manhour per equipment on the right side of the intial cell

Dim k As Integer
Dim m As Integer

ActiveCell.Offset(1 - eqListNo, 0).Select
For k = 1 To eqListNo
    For m = 1 To KiraRange
        If ActiveCell.Offset(0, m) <> "" And IsNumeric(ActiveCell.Offset(0, m).Value) Then
            ActiveCell.Offset(0, m) = ActiveCell.Offset(0, m) / eqListNo
        End If
    Next m
    ActiveCell.Offset(1, 0).Select
Next k

5. Go back to the 1st cell and delete the entire row then go to the next equipment list row to be processed.

ActiveCell.Offset(-1 - eqListNo, -1).Select


End Sub

Basically, the above codes should be able to do the things I described in my previous post. By assigning the macro to a letter, say 'b', I can just press 'ctrl + b' all the way to the last line.

Of course in my example, there are only six rows. In the real worksheets that I wanted to process, the row number ranges from just seven to 1,873 rows per sheet.

Boleh patah jari woo tekan 'ctrl + b' 1,873 kali! So...

6. Create a simple procedure to loop the above process until it reaches a blank cell.

Sub loopBreakItUp()
Do Until ActiveCell.Value = ""
    Call BreakItUp
End Sub 

Maka selamatlah jari kita dari kelenguhan.

In my module, I've entered some other codes to check whether the right cell is selected, to remove funny characters, to skip rows which do not require processing (i.e. the cell contains only one equipment). etc. But you probably wont need them anyway.

If you are a programmer or used VBA before, you probably noticed that my codes are not 'hard-coded'. This means you can probably use this code even if the format of your data is not as same as mine.

So, that's it. My first Excel 2010 VBA project. Did it to assist my colleague, Mek Yam with her tasks.

Thanks for going through it. Feedback and question are always appreciated.

p/s: You can use the above codes at your own risk. Please backup any file before you execute the VBA codes.

Friday, January 4, 2013


So, let me just share something about my first VBA project in MS Excel 2010.

I have a database of Preventive Planned Maintenance (PPM) activities saved in Excel worksheets. The PPMs are organized in a roster, where each PPM takes one row of the worksheet. For each PPM, there is a list of equipment stored in one cell, and manhours stored in a few cells along with other information.

Here's a screenshot of the original data:

I want the equipment list to be separated into individual cells in a new row with its PPM data copied, and the manhours divided per equipment.

Basically, this is the result that I want:

Before I share my codes, how would you do it?

Erm... I'm asking like someone actually read my blog... but who knows...

Thursday, January 3, 2013


I cant believe I only made one entry in 2012. Ha ha.

2012 was a rather tough year. After five years in HR, I finally moved out and became a maintenance system 'expert user' (a clueless 'expert' actually), and by default required to lead my division in a Group-wide system migration project which was finally rolled out a couple of days ago.

At the same time I was struggling with post-surgery sciatica and back pain which resulted in me to undergo one major surgery to screw two flexible metal support on my lumbar spine earlier last year, then a minor surgical procedure to 'clean-up' the area around the implant, just a couple of months ago.

Well, let just say due to the pain I was suffering, I was screwed, literally, as well as metaphorically.

That basically sum my 2012. Plus, I decided to go bald.

Anyway, less than a month ago I went for a training on Excel VBA Fundamental. Tired of using so many nested functions and formulas in cells to do what I want to do in Excel all this while, I decided to go for this training.

I regretted attending that training... 7 years too late. This is probably the most useful corporate-sponsored training I ever attended since I started working with this Company. I started using VBA right away after coming back from the training.

Well, for those of you out there whose job uses a lot of MS Excel, I would recommend that you go for this course, provided that you already know and used some of the more 'advance' feature of the software. If you do not know how to use the pivot table or even the vlookup function correctly, don't bother.

Actually, I wanted to share my first decent VBA 'project' in this blog so that others can comment or learn something from it but since getting back from office today, I was too occupied with house chores. The codes is not fully completed yet, although I've managed to get it to do the things I wanted it to do.

Wait, what did you say? Just copy + paste the codes?


If one thing I learned from 2012 is this: try not to present half-cooked material to avoid you yourself from being cooked (or grilled) by your audience.

Good night.