At last , it works !

As at tonight, only the first excel file has bugs.

I have runned my program in two other files and it works extremely well.. Yooo hoooo... at least I know that it is not my creation (or myself) the culprit to the programming problem.

Post August, I hope I could runned my merged data in neural network and works towards achieving a solution through mathematical theory of evidence.

Bugs in Excel cells

I have been working in the summer. The VBA programs that I have created do help me to merge the data. The only problem is that the data is too large and sometimes Excel becomes too busy and does not respond.

There is another problem which is unrelated to be program. No other characters can exists in each cells inclusive of those hidden unseen characters. There are millions of cells in Excel.

I am encountering this problem at row no. 5762. There are another 1000 plus rows beneath that needs to be checked and merged ! So I will take some considerable time.

Condolences to Dr. Salim Sairan's Family

I last met Dr. Salim Sairan one year ago, asking for his blessing and approval to allow me to pursue my doctorate degree in Japan. He was cherish with his ever smiling face and gave the approval instantly.

Dr. Salim is a person who is hard working, often at a lightning speed. Even many of the young engineers felt that it is difficult to catch-up with his command which needs immediate intention. Being an MD, Dr. Salim Sairan is a sort of leader who will be at the front line of charge in the battlefield, and always seen to be in command of the organisation.

His appetite for excellence pushed the organisation forward. Meetings were rapid in succession. His time isspent productively and vigorously. Perhaps, he worked too hard. How I wish that things could be better planned to reduce work stresses.

Today, when I called a colleaque in TNB Research, I was told that Dr. Salim has passed away after friday prayer, in his office on March, 28th. I think we have lost a great leader and I have lost the opportunity to meet him again after I have finished my studies.

For all good things that he has done I pray that Allah accepted his deeds, and forgive his sins.

The Art of Eating Sushi

One should try sushi when one is in Japan. The raw fish delicacies taste better in Japan than anywhere else . I guarantee that the fish is fresher in Japan. Even the soya sauce and wasabi taste better. For me, the tastiest fish is "salmon", most Japanese opted for "tuna".

I found this interesting video in youtube which I term as "The Art of Eating Sushi"

Enjoy it !

Kyoto - Western Capital of Japan

If one look carefully at the spelling of Kyoto , it appears that "Kyoto" is spelled in reversal to Japan's capital city - "Tokyo". Indeed, before the era of Edo (Tokyo) and Shogun Tokugawa, Kyoto was a capital of Japan. This period is the period of the western samurai's warlord supremacy over the easter warlord. One man turned this around - Ieyasu Tokugawa , the unifier of modern Japan.

[ The picture above is the Kiyomizu temple. The architechture is unique and very personal. Building material is wood and the structure is placed on the side of a hill. ]

Kyoto is proud to be the ancient capital of Japan. It houses numerous historical attractions which consists of photographic temples and shrines - Kiyomizu, Kinkakuji and Ginkakuji to name a few. One can also visit Kyoto Imperial Palace which was once the official residence of Japan Emperor before the Meiji Restoration in 1868.

[ The picture on the left is Kinkakuji or Golden Pavillion, in between the momiji leaves and tree]

The best time to visit Kyoto is in autumn. The period when the colour of momiji leaves turn to red. Fact is that, the colour does not have one single colour tone. It varies from pinkish, orange and yellowish. Only one caution ! Bear in mind, autumn is the period where many people( Japanese or foreigners alike) flocks at the tourist attraction places. We can have a hard time locating our family member if everyone moves individually his own direction.

[Picture on the right - Myself under a pink tree]

Enjoying the Tides at Ooarai Sun Beach , Mito

We went again to Ooarai Sun Beach to enjoy the small reptides of pacific ocean, albeit the weather forecast indicated scattered thunderstorm. Forecast being a forecast never meant to be truly believable, and it was proven that the day was nice and cloudy, thus blocking the summer sun from having a bite on our skin.

It costs us 1,000 yen for parking. The nice thing is that, they provide open-air plain-water shower, so we can rinse salty water from our back before going home. The not so nice thing, we have to walk for 100 metres to get to the beach spot. There are many spots of beach volleyball. And many restaurants . There are also a number of life-saver stations manned by people in orange clothing. It seems that the beach facilities closes at 4.30 pm...

Malisa enjoyed it well. The promise to bring her to a "water-recreation" venue is fulfilled. She seems wanting to go back again, but her ayah says in his heart ... no no no. Mikael was afraid of the waves. But once he familiarised himself with the "float" and the waves, he taught that the sea is his home !

On the way back, we stopped over at a fish market. Bought squids and chopped stingray..it is cheaper than those sold in Kasumi or Marumo supermarket.. Interesting, the fish market also sells "baby sharks."






Duhh.. I am amazed with the samurai suit in the fish market.. Hopefully will get it one before I go home to Malaysia. It will cost, a minimum of RM 650.00 and with the katana sword.. it will be a perfect japan memorabilia.

Getting Help on Excel VBA Programming from Web Community

Here is a tip to solve programming problems . Discuss the problem in this website. (www.excelforum.com)

Indeed, information technology revolution is a catalyst for productivity. Most of the solution cant be obtained from books or internet content, but is within the "thinking cap" of the internet surfer.

Good net-ethics and "online interpersonal skill" will surely help us in our hour of needs.

Merge Multiple Data of Single Unique Value from Separate Worksheet Into A New Worksheet By Using VBA

Finally, at 1.15 am, I got this program running by using my dummy data. This VBA Excel codes is very useful which I have created is very powerful such that it could reduce manual working time of complex data merging work from days to minutes.

It is my pleasure to share this codes with any interested parties. As usual this codes are customised to my worksheet data properties. I have also included msgbox pop-up to check any errors that may have occured during the running of the codes. These msgbox can be deleted once you feel that the result of the codes is satisfactory.

If you have any queries, please do not hesitate to contact me.

VBA codes :-


Sub CopywithAutoFilterToNewSheet()

Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim WSFiltered As Worksheet
Dim LastRowFilteredWorksheet As Long, LastRowIntegratedWorksheet As Long
Dim LastRowInitialWorksheet As Long
Dim NextColumn As Long
Dim x As Long, Y As Long, z As Long

Set WS1 = Worksheets("sheet1")
Set WS2 = Worksheets("sheet2")

Set FilterRange = WS1.Range("A1:C" & Rows.Count)

WS1.AutoFilterMode = False
WS2.AutoFilterMode = False

With Workbooks("TestMergeDifferentWorksheet.xlsm").Sheets("Sheet2")
LastRowInitialWorksheet = .Cells(.Rows.Count, 1).End(xlUp).Row
End With

MsgBox "No. of Rows:" & LastRowInitialWorksheet

For x = 1 To 1

NextColumn = Cells(x, Columns.Count).End(xlToLeft).Column + 1

FilterRange.AutoFilter Field:=1, Criteria1:="=" & WS2.Cells(x + 1, 1).Value
FilterRange.AutoFilter Field:=2, Criteria1:="=" & WS2.Cells(x + 1, 2).Value

WS1.AutoFilter.Range.Copy
With Workbooks("TestMergeDifferentWorksheet.xlsm").Sheets("Sheet3").Cells(x, NextColumn)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

With Workbooks("TestMergeDifferentWorksheet.xlsm").Sheets("Sheet3")
LastRowFilteredWorksheet = .Cells(.Rows.Count, NextColumn).End(xlUp).Row
End With

MsgBox "No. of Rows:" & LastRowFilteredWorksheet

For Y = 1 To LastRowFilteredWorksheet - 2
Workbooks("TestMergeDifferentWorksheet.xlsm").Sheets("Sheet4").Cells(x + 2, 1).EntireRow.Insert
Next

WS1.AutoFilter.Range.Copy
With Workbooks("TestMergeDifferentWorksheet.xlsm").Sheets("Sheet4").Cells(x, NextColumn)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

With Workbooks("TestMergeDifferentWorksheet.xlsm").Sheets("Sheet4")
LastRowIntegratedWorksheet = .Cells(.Rows.Count, 4).End(xlUp).Row
End With

MsgBox "No. of Rows:" & LastRowIntegratedWorksheet

Workbooks("TestMergeDifferentWorksheet.xlsm").Sheets("Sheet3").UsedRange.ClearContents

WS1.AutoFilterMode = False

Next

For x = 2 To LastRowInitialWorksheet

NextColumn = Cells(x, Columns.Count).End(xlToLeft).Column + 1

FilterRange.AutoFilter Field:=1, Criteria1:="=" & WS2.Cells(x + 1, 1).Value
FilterRange.AutoFilter Field:=2, Criteria1:="=" & WS2.Cells(x + 1, 2).Value

WS1.AutoFilter.Range.Copy
With Workbooks("TestMergeDifferentWorksheet.xlsm").Sheets("Sheet3").Cells(1, NextColumn)
.PasteSpecial xlPasteValues
End With

With Workbooks("TestMergeDifferentWorksheet.xlsm").Sheets("Sheet3")
LastRowFilteredWorksheet = .Cells(.Rows.Count, NextColumn).End(xlUp).Row
End With

MsgBox "No. of Rows:" & LastRowFilteredWorksheet

For Y = 1 To LastRowFilteredWorksheet - 2

Workbooks("TestMergeDifferentWorksheet.xlsm").Sheets("Sheet4").Cells(LastRowIntegratedWorksheet + 2, 1).EntireRow.Insert

Next

WS1.AutoFilter.Range.Copy

With Workbooks("TestMergeDifferentWorksheet.xlsm").Sheets("Sheet4").Cells(LastRowIntegratedWorksheet + 1, NextColumn - 3)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False

For z = 1 To LastRowFilteredWorksheet
Cells(z + LastRowIntegratedWorksheet + 1, 4).Cut Cells(z + LastRowIntegratedWorksheet, 4)
Cells(z + LastRowIntegratedWorksheet + 1, 5).Cut Cells(z + LastRowIntegratedWorksheet, 5)
Cells(z + LastRowIntegratedWorksheet + 1, 6).Cut Cells(z + LastRowIntegratedWorksheet, 6)
Next
End With

With Workbooks("TestMergeDifferentWorksheet.xlsm").Sheets("Sheet4")
LastRowIntegratedWorksheet = .Cells(.Rows.Count, 4).End(xlUp).Row
End With

MsgBox "No. of Rows:" & LastRowIntegratedWorksheet

Workbooks("TestMergeDifferentWorksheet.xlsm").Sheets("Sheet3").UsedRange.ClearContents

WS1.AutoFilterMode = False

Next

End Sub

Combining 2 database from excel worksheets by using VBA

My work involved analysis of numerous data and these data are located in several different databases of Excel worksheets.

So, the first step would be the integration of these data into one single worksheet.

I have been pondering a technique - using autofilter for a few days and developed a few modules leading to my objectives.

As at yesterday, I got the module right for my desired objective. The codes in the module are customised to my sample databases. Application on other database needs some modification. If you need the advise, please do not hesistate to email me.

BTW, for sharing purpose, codes are written as below:-

Sub CopywithAutoFilterToExistingSheet()

Dim WS4 As Worksheet
Dim WS5 As Worksheet
Dim WSFiltered As Worksheet

Dim FilterRange As Range, Range1 As Range, Range2 As Range

Dim NextColumn As Long
Dim x As Long


Set WS4 = Worksheets("sheet4")
Set WS5 = Worksheets("sheet5")

Set FilterRange = WS4.Range("A1:C" & Rows.Count)

WS4.AutoFilterMode = False
WS5.AutoFilterMode = False


For x = 1 To 1

NextColumn = Cells(x, Columns.Count).End(xlToLeft).Column + 1

FilterRange.AutoFilter Field:=1, Criteria1:="=" & WS5.Cells(x + 1, 1).Value
FilterRange.AutoFilter Field:=2, Criteria1:="=" & WS5.Cells(x + 1, 2).Value

WS4.AutoFilter.Range.Copy
With Workbooks("TestEnteringValueAtNextEmptyCellColumn.xlsm").Sheets("Sheet5").Cells(x, NextColumn)
.PasteSpecial xlPasteValues

End With

WS4.AutoFilterMode = False

Next



For x = 2 To 10

NextColumn = Cells(x, Columns.Count).End(xlToLeft).Column + 1

FilterRange.AutoFilter Field:=1, Criteria1:="=" & WS5.Cells(x + 1, 1).Value
FilterRange.AutoFilter Field:=2, Criteria1:="=" & WS5.Cells(x + 1, 2).Value

WS4.AutoFilter.Range.Copy
With Workbooks("TestEnteringValueAtNextEmptyCellColumn.xlsm").Sheets("Sheet5").Cells(x + 1, NextColumn - 3)
.PasteSpecial xlPasteValues

Cells(x + 2, 4).Cut Cells(x + 1, 4)
Cells(x + 2, 5).Cut Cells(x + 1, 5)
Cells(x + 2, 6).Cut Cells(x + 1, 6)

End With


WS4.AutoFilterMode = False

Next


End Sub

VBA can do wonder with Excel

Indeed, VBA is a tool for productivity in case the object is Excel. I simply did not see the "power" of VBA during those working days and I have left C language programming since I completed my bachelor degree 14 years ago.

Having thousands of data on hand to be analysed, manual work and calculation are simply too consuming in term of time and the possibility of making mistakes.

After combining data for 2 months , I given up to do things manually. So I invested more than RM 500 for two books , Excel 2007 Power Programming with VBA and Excel 2007 VBA programming for dummies, both authored by John Walkenbach and received them early this months. I simply became engrossed with the detailed explaination given by John Walkenbach.

It took me three weeks to go through the first 11 chapters and now I have gained confidence back in programming. As it is now, I have written a number of modules customised to my need during the initial phase of this study. Criss-crossing double checking the book and internet resources.

I am glad that I have invested in knowledge and will keep up with sharing the knowledge for the benifit of anyone interested !

Use VBA to find next empty Column in Excel Worksheet and enter user-input values to the column

I got this module correct by yesterday. It is quite useful for combining old data with new data.

This example assume next data to be entered are within the values of two parameters (1) "gender" and (2) "place". User needs to enter the data on the input box and the data will appears at the next empty colum in the worksheet. This codes also assume tha the worksheet has "title header in row 1".


Sub GetData3()
Dim NextColumn As Long
Dim Entry1 As String, Entry2 As String
Dim x As Long, y As Long

Do
For x = 1 To 100

NextColumn = Cells(x + 1, Columns.Count).End(xlToLeft).Column + 1

Entry1 = InputBox("Enter Place")
If Entry1 = "" Then Exit Sub
Entry2 = InputBox("Enter Gender")
If Entry2 = "" Then Exit Sub

Cells(x + 1, NextColumn) = Entry1
Cells(x + 1, NextColumn + 1) = Entry2
Next
Loop

End Sub

Ooarai - A seaside town near Tsukuba

Approximately 50 km from Tsukuba, Ooarai is a seaside town where people usually go in the summer. Other than swimming and sun-bathing, tourist attraction facilities such as Ooarai Aquarium and Ooarai Sun Beach Water Theme Park pull crowds to this small city. Near - yet so far. It took one and a half hours to reach Ooarai. Indeed, traffic moves slowly.

We went to Ooarai Aquarium with tickets courtesy from Affendi-San. Price of a ticket is 1,800 Yen per person. It is expensive but I think it worth the money - Ooarai Aqua consists of a multi-story aquarium , an arena for dolphin-show, a watch-tower and it is located besides a beach. Getting the ticket free is a bonus.

Malisa enjoyed the dolphin show. Mikael was initially surprised by the splashes of water., giggled and hugged us hard. Overall it was a nice adventure for the kids.

Food was packed from home. We made "Nasi goreng" with "tempe" and "sambal ikan bilis" for lunch and Affendi-San brought "udon" and "tsuyu" for tea-time.

Overall it was an interesting visit. Tsukareta desuga tanoshi desu !

Before we leave, Malisa picked up a theme park's pamphlet and asserted her wishes to go there . So, we will plan for another visit to Ooarai this summer, insya Allah, if our finance allowed us and perhap's her Auntie Sue will be free to tag along.

Sayounara !

Lavender Blossoms in Summer

It is now July and it is summer term in Japan and from July to August, University of Tsukuba is having it's summer vacation. That is only true for undergraduate student. As for me, I can not afford a vacation until I feel that there is less work for my degree . But let me take a rest "in between" the works. I have just finished an essay in Random Fuzzy Variables.

As I was parking my bicycle in the university's campus, I came across the scent which I was so familiar with back home, the scent of lavender. Omoshiroi ! lavender plants are just a metre away.

It seems that the purple plants blossom during summer and I saw lots of them in a few separate locations in the university. And there are bees harmlessly surrounding the lavender plants. I wonder why the bees concentrate most on lavender plant, whereas there are a lot of other plants spotted nearby. Maybe, it's scent does not only attract human but also affectively "seduced" the bees. Well, this gives an impression that there is sort of honey call "lavender honey".

I have attached a picture of lavender in this post since a picture tells a thousand words which I myself may unable to describe.

Since lavender blossoms in summer and Malaysia is always in summer the whole year round, it gives me the second impression - lavender can be planted in Malaysia. An idea prop-up - "Buy lavender seeds and plant it in Malaysia". If it is suffessful, then I dont have to buy anymore air freshener for my car and my room. Not only, it is nice to smell, it has decorative attributes.

By the way, in my self-made frugal initiative, I am already a small part-time planter in Japan - if you visit me in Japan you can have some freshly picked chillies and tomatoes. Next, on my list is broccoli.

The Benifits Being A CIGRE Member For Doctorate Student

Being a student and researcher, I have to read a number of research results and discussion by other researchers. This is more important in the case of a doctorate student as the student needs to produce journal papers in order to have originality in the idea for the paper to be accepted as article of the journal. That is not to repeat other's research. Intensive literature review is needed to ensure that we do not repeat whatever others has done in our pursit of the degree.

So, the starting point of a doctorate student is search of papers or literature review and over the years, I have obtained a lot of articles in IEEE website, through Uniten's library subscription of IEEE journals.

However, since Uniten did not subsribe to CIGRE journal, I had missed the opportunity to obtain the knowledge and resources free of charge. In addition, Tsukuba University which I am affiliated now also do not subscribe to CIGRE.

CIGRE journals are expensive. It costs a minimum of 25 Euro for one journal and a maximum of 40 Euro on the expensive side. So, I decided to become a member of CIGRE -Japanese National Comittee in order to try my luck in getting any discounts for these journals. So, I wrote an email to Ms. Naomi Kanechika , visited her to pay the registration fee of CIGRE and I am now a CIGRE member.

I did the search on CIGRE website today - It is to my surprise that I can now download all the journals for free !

A great addition to the invitation as a young delegate to CIGRE conference this August 2008 !

My First Examination in Risk Engineering.

Last Friday I went through an examination in Soft Computing subject which was taught by Prof. Miyamoto Sadaaki. The lessons were in Japanese, but mathematics being a universal language has helped me to understand the concepts of fuzzy logic, modal logic, rough sets and fuzzy sets.

In my opinion, a student with limited knowledge in Japanese can do well in his studies if the lessons are 100% mathematics and logics, but will have big problems if the lessons are social and human studies, law or etc.

It is exhilarating to have finished an exam of which the lesson was taught mostly in Japanese language. Prof. Miyamoto Sadaaki has really help by writing quotes and formulaes on the board in English language.

I think I have done well in answering the questions except there is one silly mistake that I spotted after the examination. Silly it is and clumsy I was. Nevertheless, as I did in most of my Master's degree examination, I finished early and went out of the examination hall about 30 minutes before time.

Exhilarating it was and with the knowledge that Prof. Miyamoto has imparted enabled me to understand the journals which was previously understood by a distant. I still have to submit a report to him by next Friday.