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
Selection.EntireRow.Insert
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
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
Selection.EntireRow.Delete
ActiveCell.offset(eqListNo,0).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
Loop
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.
