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