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.