Manivannan's Blog

Web query to get zip code from Google or Yahoo!

noreply@blogger.com (Manivannan) — Mon, 21 Jun 2010 19:42:00 +0000

'Using web query to get zip code from Google or Yahoo!
'This required street address, city, state fields.

Sub Get_Zip_Code_From_Google_Or_Yahoo()
Dim gurl As String
Dim qstr, cname, street, city, state, zip, street_city_state, street_city_state_1, street_city_state_2, street_city_state_3, street_city_state_4 As String
Dim rc1, rca2, rcb2, pos, pos1, pos2, pos3, pos4 As Integer
Dim tm As Single
rc1 = Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
For i = 4144 To rc1
If Worksheets(1).Cells(i, 6) <> "" Then
GoTo again
End If
zip = ""
street_city_state = street_city_state_1 = street_city_state_2 = street_city_state_3 = street_city_state_3 = ""
pos = 0
cname = Replace(Worksheets(1).Cells(i, 2), "&", "%26")
street = Worksheets(1).Cells(i, 3)
city = Worksheets(1).Cells(i, 4)
state = Worksheets(1).Cells(i, 5)
street_city_state_1 = LCase(Trim(street)) & ", " & LCase(Trim(city)) & ", " & LCase(Trim(state))
street_city_state_2 = LCase(Trim(street)) & " " & LCase(Trim(city)) & ", " & LCase(Trim(state))
street_city_state_3 = LCase(Trim(street)) & " " & LCase(Trim(city)) & " " & LCase(Trim(state))
street_city_state_4 = LCase(Trim(street)) & ", " & LCase(Trim(city)) & ", " & LCase(Trim(state)) & ","
street_city_state_5 = LCase(Trim(street)) & " " & LCase(Trim(city)) & ", " & LCase(Trim(state)) & ","
qstr = cname & "+" & Worksheets(1).Cells(i, 3) & "+" & Worksheets(1).Cells(i, 4) & "+" & _
Worksheets(1).Cells(i, 5)
'google search
gurl = "http://www.google.com/search?q=" & qstr
'yahoo search
'gurl = "http://www.altavista.com/web/results?fr=altavista&itag=ody&q=" & qstr & "+&kgs=1&kls=0"
Worksheets(2).Activate
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & gurl, _
Destination:=Range("$A$1"))
.Name = ""
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

rca2 = Worksheets(2).Range("A" & Rows.Count).End(xlUp).Row
rcb2 = Worksheets(2).Range("B" & Rows.Count).End(xlUp).Row

For j = 1 To rcb2
pos = InStr(LCase(Worksheets(2).Cells(j, 2)), LCase(Trim(Worksheets(1).Cells(i, 4)) & ", " & Trim(Worksheets(1).Cells(i, 5))))
If pos > 0 Then
zip = Mid(Worksheets(2).Cells(j, 2), Len(Worksheets(1).Cells(i, 4)) + Len(Worksheets(1).Cells(i, 5)) + 2 + 2, Len(Worksheets(2).Cells(j, 2)))
If InStr(zip, "-") > 0 Then
Worksheets(1).Cells(i, 6) = Left(zip, InStr(zip, "-") - 1)
Else
Worksheets(1).Cells(i, 6) = zip
End If
Exit For
End If
Next

If j > rcb2 Then
For k = 1 To rca2
pos1 = InStr(LCase(Worksheets(2).Cells(k, 1)), street_city_state_1)
pos2 = InStr(LCase(Worksheets(2).Cells(k, 1)), street_city_state_2)
pos3 = InStr(LCase(Worksheets(2).Cells(k, 1)), street_city_state_3)
pos4 = InStr(LCase(Worksheets(2).Cells(k, 1)), street_city_state_4)
pos5 = InStr(LCase(Worksheets(2).Cells(k, 1)), street_city_state_5)

If pos1 > 0 Then
pos = pos1
street_city_state = street_city_state_1
ElseIf pos2 > 0 Then
pos = pos2
street_city_state = street_city_state_2
ElseIf pos3 > 0 Then
pos = pos3
street_city_state = street_city_state_3
ElseIf pos4 > 0 Then
pos = pos4
street_city_state = street_city_state_4
ElseIf pos5 > 0 Then
pos = pos5
street_city_state = street_city_state_5
Else
pos = 0
End If

If pos1 > 0 And pos4 > 0 Then
pos = pos + 1
End If

If pos2 > 0 And pos5 > 0 Then
pos = pos + 1
End If

If pos > 0 Then
zip = Trim(Mid(Worksheets(2).Cells(k, 1), pos + Len(street_city_state) + 1, 5))
If IsNumeric(zip) = True Then
Worksheets(1).Cells(i, 6) = zip
Exit For
End If
End If
Next
End If

Worksheets(2).Range("A:Z").Clear
tm = Timer
While (Timer - tm < 3)
Wend
again:
Next
End Sub

Macro to consolidate excel files into one

noreply@blogger.com (Manivannan) — Mon, 21 Jun 2010 19:33:00 +0000

Sub File_Consolidation()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim wkb1 As Workbook
Dim rcs, ccs, rct, cct As Integer
Dim awkb, fpath As String
'Folder path
fpath = "C:\Manivannan\"

Set objFSO = CreateObject("Scripting.FileSystemObject")
awkb = ActiveWorkbook.Name
Set objFolder = objFSO.GetFolder(fpath)

For Each objFile In objFolder.Files
If InStr(objFile.Name, awkb) = 0 Then
Set wkb1 = Workbooks.Open(fpath & objFile.Name)
rct = Workbooks(awkb).Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
rcs = wkb1.Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
ccs = wkb1.Worksheets(1).Cells(1, Columns.Count).End(xlToLeft).Column

'Files with header
'wkb1.Worksheets(1).Range(wkb1.Worksheets(1).Cells(2, 1), 'wkb1.Worksheets(1).Cells(rcs, ccs)).Copy _
' Destination:=Workbooks(awkb).Worksheets(1).Range("A" & rct + 1)

'Files without header
wkb1.Worksheets(1).Range(wkb1.Worksheets(1).Cells(1, 1), wkb1.Worksheets(1).Cells(rcs, ccs)).Copy _
Destination:=Workbooks(awkb).Worksheets(1).Range("A" & rct + 1)

Workbooks(awkb).Save
wkb1.Close SaveChanges:=False
End If
Next objFile
End Sub

Macro to get URL from hyperlink

noreply@blogger.com (Manivannan) — Wed, 26 May 2010 09:35:00 +0000

'Friendly Name Hyperlink
'Google http://www.google.co.in/
'Yahoo! http://in.yahoo.com/

Function GetURL(Links As Range)
GetURL = Links.Hyperlinks(1).Address
End Function

Excel macro to generate monthly calendar

noreply@blogger.com (Manivannan) — Thu, 13 May 2010 17:51:00 +0000

Sub monthcal()
'To generate month calendar

Dim m, y, r, i, maxdt As Integer
Dim dt As String

'Array declaration
Dim mname
mname = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")

Range("A:G").Clear 'To clear range
m = CInt(InputBox("Enter month (1-12) : "))
y = CInt(InputBox("Enter year (YYYY) : "))

Cells(1, 1) = "Sunday"
Cells(1, 2) = "Monday"
Cells(1, 3) = "Tuesday"
Cells(1, 4) = "Wednesday"
Cells(1, 5) = "Thursday"
Cells(1, 6) = "Friday"
Cells(1, 7) = "Saturday"

Range("A1:G1").Font.Bold = True
Range("A1:G1").Interior.Color = vbBlack
Range("A1:G1").Font.Color = vbWhite

Worksheets(1).Name = mname(m - 1) & "-" & y 'to change sheet name

r = 2

maxdt = Day(DateAdd("d", -1, DateSerial(y, m + 1, 1))) 'To find last day of a month
For i = 1 To maxdt
Cells(r, Weekday(DateSerial(y, m, i))).RowHeight = 40 'To change row height
If Weekday(DateSerial(y, m, i)) = 1 Then
Cells(r, Weekday(DateSerial(y, m, i))).Font.Color = vbRed
End If

If Weekday(DateSerial(y, m, i)) = 7 Then
Cells(r, Weekday(DateSerial(y, m, i))) = Day(DateSerial(y, m, i))
r = r + 1
Else
Cells(r, Weekday(DateSerial(y, m, i))) = Day(DateSerial(y, m, i))
End If
Next
End Sub

Adding time delay to excel macro

noreply@blogger.com (Manivannan) — Thu, 13 May 2010 17:36:00 +0000

Sub Time_Delay()
'Adding 3 seconds time delay using Timer
Dim mtime As Single
For i = 1 To 10
mtime = Timer
Worksheets(1).Cells(i, 1) = i
While (Timer - mtime <= 3) '3 seconds delay
Wend
Next
End Sub

Excel macro to remove double space, leading and trailing spaces

noreply@blogger.com (Manivannan) — Thu, 13 May 2010 17:35:00 +0000

'To remove double spaces, leading and trailing spaces

Sub Rem_Double_Spaces()
Dim rc, cc As Long
Dim s As Variant
Dim t As String
Dim i, j As Integer
rc = Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
cc = Worksheets(1).Cells(1, Columns.Count).End(xlToLeft).Column
t = ""
For i = 2 To rc
For j = 1 To cc
s = Split(Worksheets(1).Cells(i, j), , -1)
Worksheets(1).Cells(i, j) = ""
For k = LBound(s) To UBound(s)
If s(k) <> "" Then
t = t & s(k) & " "
End If
Next k
Worksheets(1).Cells(i, j) = Trim(t)
t = ""
Next j
Next i
End Sub

Excel 2007 Functions

noreply@blogger.com (Manivannan) — Tue, 29 Dec 2009 06:37:00 +0000

Date and time functions
DATE - Returns the serial number of a particular date
DATEVALUE - Converts a date in the form of text to a serial number
DAY - Converts a serial number to a day of the month
DAYS360 - Calculates the number of days between two dates based on a 360-day year
EDATE - Returns the serial number of the date that is the indicated number of months before or after the start date
EOMONTH - Returns the serial number of the last day of the month before or after a specified number of months
HOUR - Converts a serial number to an hour
MINUTE - Converts a serial number to a minute
MONTH - Converts a serial number to a month
NETWORKDAYS - Returns the number of whole workdays between two dates
NOW - Returns the serial number of the current date and time
SECOND - Converts a serial number to a second
TIME - Returns the serial number of a particular time
TIMEVALUE - Converts a time in the form of text to a serial number
TODAY - Returns the serial number of today's date
WEEKDAY - Converts a serial number to a day of the week
WEEKNUM - Converts a serial number to a number representing where the week falls numerically with a year
WORKDAY - Returns the serial number of the date before or after a specified number of workdays
YEAR - Converts a serial number to a year
YEARFRAC - Returns the year fraction representing the number of whole days between start_date and end_date
Engineering functions
BIN2DEC - Converts a binary number to decimal
BIN2HEX - Converts a binary number to hexadecimal
BIN2OCT - Converts a binary number to octal
CONVERT - Converts a number from one measurement system to another
DEC2BIN - Converts a decimal number to binary
DEC2HEX - Converts a decimal number to hexadecimal
DEC2OCT - Converts a decimal number to octal
HEX2BIN - Converts a hexadecimal number to binary
HEX2DEC - Converts a hexadecimal number to decimal
HEX2OCT - Converts a hexadecimal number to octal
OCT2BIN - Converts an octal number to binary
OCT2DEC - Converts an octal number to decimal
OCT2HEX - Converts an octal number to hexadecimal
Information functions
CELL - Returns information about the formatting, location, or contents of a cell
ISBLANK - Returns TRUE if the value is blank
ISERR - Returns TRUE if the value is any error value except #N/A
ISERROR - Returns TRUE if the value is any error value
ISEVEN - Returns TRUE if the number is even
ISLOGICAL - Returns TRUE if the value is a logical value
ISNA - Returns TRUE if the value is the #N/A error value
ISNONTEXT - Returns TRUE if the value is not text
ISNUMBER - Returns TRUE if the value is a number
ISODD - Returns TRUE if the number is odd
ISREF - Returns TRUE if the value is a reference
ISTEXT - Returns TRUE if the value is text
NA - Returns the error value #N/A
Logical functions
AND - Returns TRUE if all of its arguments are TRUE
FALSE - Returns the logical value FALSE
IF - Specifies a logical test to perform
IFERROR - Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
NOT - Reverses the logic of its argument
OR - Returns TRUE if any argument is TRUE
TRUE - Returns the logical value TRUE
Lookup and reference functions
ADDRESS - Returns a reference as text to a single cell in a worksheet
AREAS - Returns the number of areas in a reference
COLUMN - Returns the column number of a reference
COLUMNS - Returns the number of columns in a reference
HLOOKUP - Looks in the top row of an array and returns the value of the indicated cell
HYPERLINK - Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet
LOOKUP - Looks up values in a vector or array
ROW - Returns the row number of a reference
ROWS - Returns the number of rows in a reference
VLOOKUP - Looks in the first column of an array and moves across the row to return the value of a cell
Math and trigonometry functions
ABS - Returns the absolute value of a number
ACOS - Returns the arccosine of a number
ACOSH - Returns the inverse hyperbolic cosine of a number
ASIN - Returns the arcsine of a number
ASINH - Returns the inverse hyperbolic sine of a number
ATAN - Returns the arctangent of a number
ATAN2 - Returns the arctangent from x- and y-coordinates
ATANH - Returns the inverse hyperbolic tangent of a number
CEILING - Rounds a number to the nearest integer or to the nearest multiple of significance
COS - Returns the cosine of a number
COSH - Returns the hyperbolic cosine of a number
DEGREES - Converts radians to degrees
EVEN - Rounds a number up to the nearest even integer
EXP - Returns e raised to the power of a given number
FACT - Returns the factorial of a number
FACTDOUBLE - Returns the double factorial of a number
FLOOR - Rounds a number down, toward zero
GCD - Returns the greatest common divisor
INT - Rounds a number down to the nearest integer
LCM - Returns the least common multiple
LN - Returns the natural logarithm of a number
LOG - Returns the logarithm of a number to a specified base
LOG10 - Returns the base-10 logarithm of a number
MOD - Returns the remainder from division
ODD - Rounds a number up to the nearest odd integer
PI - Returns the value of pi
POWER - Returns the result of a number raised to a power
PRODUCT - Multiplies its arguments
QUOTIENT - Returns the integer portion of a division
RADIANS - Converts degrees to radians
RAND - Returns a random number between 0 and 1
RANDBETWEEN - Returns a random number between the numbers you specify
ROMAN - Converts an arabic numeral to roman, as text
ROUND - Rounds a number to a specified number of digits
ROUNDDOWN - Rounds a number down, toward zero
ROUNDUP - Rounds a number up, away from zero
SERIESSUM - Returns the sum of a power series based on the formula
SIGN - Returns the sign of a number
SIN - Returns the sine of the given angle
SINH - Returns the hyperbolic sine of a number
SQRT - Returns a positive square root
SQRTPI - Returns the square root of (number * pi)
SUM - Adds its arguments
SUMIF - Adds the cells specified by a given criteria
SUMIFS - Adds the cells in a range that meet multiple criteria
SUMPRODUCT - Returns the sum of the products of corresponding array components
SUMSQ - Returns the sum of the squares of the arguments
TAN - Returns the tangent of a number
TANH - Returns the hyperbolic tangent of a number
TRUNC - Truncates a number to an integer
Statistical functions
AVERAGE - Returns the average of its arguments
AVERAGEA - Returns the average of its arguments, including numbers, text, and logical values
AVERAGEIF - Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria
AVERAGEIFS - Returns the average (arithmetic mean) of all cells that meet multiple criteria.
COUNT - Counts how many numbers are in the list of arguments
COUNTA - Counts how many values are in the list of arguments
COUNTBLANK - Counts the number of blank cells within a range
COUNTIF - Counts the number of cells within a range that meet the given criteria
COUNTIFS - Counts the number of cells within a range that meet multiple criteria
MAX - Returns the maximum value in a list of arguments
MAXA - Returns the maximum value in a list of arguments, including numbers, text, and logical values
MEDIAN - Returns the median of the given numbers
MIN - Returns the minimum value in a list of arguments
MINA - Returns the smallest value in a list of arguments, including numbers, text, and logical values
MODE - Returns the most common value in a data set
Text functions
ASC - Changes full-width (double-byte) English letters or katakana within a character string to half-width (single-byte) characters
CHAR - Returns the character specified by the code number
CLEAN - Removes all nonprintable characters from text
CODE - Returns a numeric code for the first character in a text string
CONCATENATE - Joins several text items into one text item
EXACT - Checks to see if two text values are identical
FIND, FINDB - Finds one text value within another (case-sensitive)
FIXED - Formats a number as text with a fixed number of decimals
LEFT, LEFTB - Returns the leftmost characters from a text value
LEN, LENB - Returns the number of characters in a text string
LOWER - Converts text to lowercase
MID, MIDB - Returns a specific number of characters from a text string starting at the position you specify
PROPER - Capitalizes the first letter in each word of a text value
REPLACE, REPLACEB - Replaces characters within text
REPT - Repeats text a given number of times
RIGHT, RIGHTB - Returns the rightmost characters from a text value
SEARCH, SEARCHB - Finds one text value within another (not case-sensitive)
SUBSTITUTE - Substitutes new text for old text in a text string
TEXT - Formats a number and converts it to text
TRIM - Removes spaces from text
UPPER - Converts text to uppercase
VALUE - Converts a text argument to a number

Excel 2007 Shortcuts

noreply@blogger.com (Manivannan) — Mon, 28 Dec 2009 15:24:00 +0000

1. CTRL+X - Cuts the selected cells
2. CTRL+C - Copies the selected cells
3. CTRL+V - Paste
4. CTRL+ALT+V - Paste Special dialog box
5. CTRL+Z - Undo
6. CTRL+Y - Redo
7. F2 - Edits the active cell
8. SHIFT+F2 - Adds or edits a cell comment
9. CTRL+D - Fill down
10. CTRL+SHIFT+L or ALT+D+F+F - Filter
11. CTRL+1 - Opens the Format Cells dialog box
12. CTRL+SHIFT+P - Opens the Format Cells dialog box with Font tab selected
13. CTRL+B or CTRL+2 - Bold
14. CTRL+I or CTRL+3 - Italic
15. CTRL+U or CTRL+4 - Underline
16. CTRL+5 - Strikethrough
17. CTRL+6 - Alternates between hiding objects, displaying objects, and displaying placeholders for objects.
18. CTRL+F or SHIFT+F5 - Find
19. CTRL+H - Find&Replace
20. SHIFT+F4 - Repeat the last Find action
21. CTRL+P - Print
22. CTRL+F2 - Print preview
23. CTRL+O - Open
24. CTRL+S - Save
25. CTRL+F4 or CTRL+W - Closes the selected workbook window
26. ALT+F4 - Close Excel
27. CTRL+ARROW - Move to edge of region
28. CTRL+* - Select current region
29. CTRL+A or CTRL+SHIFT+SPACEBAR - selects the entire worksheet.
30. CTRL+HOME - Select cell A1
31. CTRL+END - Select last cell in used range
32. CTRL+SHIFT+HOME - Select from active cell to A1
33. CTRL+SHIFT+END - Select from active cell to last cell in used range
34. PAGE DOWN - Moves one screen down in a worksheet.
35. CTRL+PAGE DOWN - Move to the next sheet
36. ALT+PAGE DOWN - Moves one screen to the right in a worksheet
37. CTRL+SHIFT+PAGE DOWN - Selects the current and next sheet in a workbook.
38. PAGE UP - Moves one screen up in a worksheet
39. CTRL+PAGE UP - Move to the previous sheet
40. ALT+PAGE UP - Moves one screen to the left in a worksheet
41. CTRL+SHIFT+PAGE UP - Selects the current and previous sheet in a workbook
42. TAB - Moves to the next cell in a worksheet or the next option in a dialog box.
43. SHIFT+TAB - Moves to the previous cell in a worksheet or the previous option in a dialog box.
44. CTRL+TAB - Switches to the next tab in dialog box
45. CTRL+SHIFT+TAB - switches to the previous tab in a dialog box.
46. CTRL+N - Open new workbook
47. SHIFT+F11 or ALT+SHIFT+F1 - Insert new worksheet
48. SHIFT+F3 - Opens Insert Function dialog box
49. CTRL+SPACEBAR - Selects an entire column in a worksheet
50. SHIFT+SPACEBAR - Selects an entire row in a worksheet
51. ALT+SPACEBAR - Displays the Control menu for the Microsoft Office Excel window
52. CTRL+SHIFT+= - Insert cell/row/column
53. CTRL+- - Delete cell/row/column
54. F5, CTRL+G - Go to
55. CTRL+9 - Hide row
56. CTRL+0 - Hide column
57. CTRL+SHFT+( - Unhide row
58. CTRL+SHFT+) - Unhide column
59. CTRL+` - Show/Hide Formulas
60. CTRL+K - Hyperlink dialog box
61. CTRL+SHIFT+& - Applies the outline border to the selected cells.
62. CTRL+SHIFT_ - Removes the outline border from the selected cells.
63. CTRL+SHIFT+~ - Applies the General number format.
64. CTRL+SHIFT+$ - Applies the Currency format with two decimal places (negative numbers in parentheses).
65. CTRL+SHIFT+% - Applies the Percentage format with no decimal places.
66. CTRL+SHIFT+^ - Applies the Exponential number format with two decimal places
67. CTRL+SHIFT+^ - Applies the Date format with the day, month, and year.
68. CTRL+SHIFT+@ - Applies the Time format with the hour and minute, and AM or PM.
69. CTRL+SHIFT+! - Applies the Number format with two decimal places, thousands separator, and minus sign (-) for negative values
70. CTRL+SHIFT+* - Selects the current region around the active cell (the data area enclosed by blank rows and blank columns).
71. CTRL+; - Current date
72. CTRL+SHIFT+: - Current time
73. CTRL+SHIFT+" - Copies the value from the cell above the active cell into the cell or the Formula Bar.
74. CTRL+' - Copies a formula from the cell above the active cell into the cell or the Formula Bar.
75. CTRL+SHIFT+O - selects all cells that contain comments
76. CTRL+R - To copy the contents and format of the leftmost cell
77. CTRL+SHIFT+U - Expanding and collapsing of the formula bar
78. CTRL+F1 - Displays or hides the Ribbon
79. F6 - Switches between the worksheet, Ribbon, task pane, and Zoom controls
80. CTRL+F6 - Switches to the next workbook window
81. CTRL+F7 - Performs the Move command on the workbook window when it is not maximized. Use the arrow keys to move the window, and when finished press ENTER, or ESC to cancel.
82. F8 - Turns extend mode on or off.
83. SHIFT+F8 - Enables you to add a nonadjacent cell or range to a selection of cells by using the arrow keys.
84. CTRL+F8 - Performs the Size command when a workbook is not maximized.
85. CTRL+F9 - Minimizes a workbook window to an icon
86. CTRL+F10 - Maximizes or restores the selected workbook window
87. SHIFT+F10 - Displays the shortcut menu for a selected item
88. SHIFT+ARROW KEY - Extends the selection of cells by one cell
89. CTRL+SHIFT+ARROW KEY - Extends the selection of cells to the last nonblank cell in the same column or row
90. ALT+ENTER - starts a new line in the same cell
91. SHIFT+ENTER - Completes a cell entry and selects the cell above
92. CTRL+T - Displays the Create Table dialog box
93. ALT+= - Autosum
94. F7 - Spelling
95. ALT+F11 - Open VBE
96. ALT+F8 - View macro
97. CTRL+F3 - Define name
98. F3 - Paste name
99. F11 or ALT+F1 - Create a chart that uses the current range

To remove leading and trailing spaces

noreply@blogger.com (Manivannan) — Wed, 21 Oct 2009 12:32:00 +0000

Sub Remove_Leading_Trailing_Spaces()
Dim rc As Long
rc = Range("A" & Rows.Count).End(xlUp).Row
cc = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To rc
For j = 1 To cc
Cells(i, j) = Trim(Cells(i, j))
Next
Next
End Sub

SQL - Lesson 3

noreply@blogger.com (Manivannan) — Thu, 04 Jun 2009 14:48:00 +0000

I assume that you all referred my previous SQL lessons

And I hope you all able to create a basic table using create table command and insert values to table called employee using insert command.

Employee table has 5 fields empno, name, job, sal, deptno and assume that you want add value to following 2 fields empno and name.

Syntax

insert into employee (empno,name) values (1004,'Jayakumar');

Here only we need to specify the required fields before values

SQL - Lesson 2

noreply@blogger.com (Manivannan) — Wed, 03 Jun 2009 15:12:00 +0000

Table is the collection of columns and rows were we can store information.

Assume that we want maintain employee information, for the first we need table structure called Employee.

Example - Employee table
Empno Name Job Sal Deptno
1001 Manivannan Manager 25000 10
1002 Jegadesh TL 15000 10
1003 Guru Executive 10000 20

First we need create a table structure using the below DDL commands
Syntax
1. Create table employee (empno int,name varchar(30),job varchar(30),sal int(6,2),deptno int);

To insert records into the employee table use below command
Insert into employee values (1001,'Manivannan','Manager',25000,10);
Insert into employee values (1002,'Jegadesh','TL',15000,10);
Insert into employee values (1003,'Guru','Executive',10000,20);

SQL - Lesson 1

noreply@blogger.com (Manivannan) — Tue, 02 Jun 2009 15:51:00 +0000

SQL stands for Structured Query Language and It was provided by various vendors like
Oracle corp, Microsoft etc.

SQL can be divided in two categories

1. DDL - Data Definition Language
2. DML - Data manupulation Language

Database is an interface to store and retrieve information in a secured way.

SQL is a set of english queries to do the database activities in a easy and efficient manner.

DDL Commands

Create, Alter, Drop

DML Commands

Select, Insert, Update, Delete

To read lines from text file

noreply@blogger.com (Manivannan) — Fri, 29 May 2009 15:22:00 +0000

To read lines from a text file and to display each lines in excels

Sub rdline()
Dim temp As String
Dim i As Integer
Open "newfile.txt" For Input As #1
i = 1
While Not EOF(1)
Line Input #1, temp
Cells(i, 1) = temp
i = i + 1
Wend
Close #1
End Sub

To add set of numbers in Excel

noreply@blogger.com (Manivannan) — Sat, 23 May 2009 12:05:00 +0000

Excel Macros
Suppose you want add number in a particular column and assume that you don't know how many number in that column

Then you can use the below excel macro to get result.

Sub add_num()
Sheets("sheet1").Select
Cells(4, 1).Select 'Assuming column 1 and number starts from row 4
ActiveCell.End(xlDown).Select
n = ActiveCell.Row
sm = 0
For j = 4 To n
sm = sm + Cells(j, 1)
Next
Cells(n + 1, 1) = sm
End Sub

The Best Free Software of 2009

noreply@blogger.com (Manivannan) — Sat, 23 May 2009 10:40:00 +0000

All we need the best and it is absolutely free then why need to spend for the paid one try it out!!!......

The best things in life often actually are free. Here, a list of 173 of the best things in life—free software, for App Launchers, Audio/Music, Backup, Blogging, Browsers, Calendar/PIMs, Communication/E-Mail, Conferencing, File Transfer/Download, File Viewers/Converters, Finance, Fun/Home, Graphics, IMs, Interface Enhancers, Local Search, Office, Operating Systems, Networking, RSS Readers, Synchronization, Video.

Sentence Case

noreply@blogger.com (Manivannan) — Sat, 23 May 2009 09:34:00 +0000

Copy and paste required sentences in first cell in excel and run the below excel macros to see your original sentences getting converted to sentence case. Try out....

Sub sentcase()
Dim s As String
n = 1
s = Cells(1, 1).Value
For i = 1 To Len(s)
If (LCase(Mid(s, i, 1)) >= "a" And LCase(Mid(s, i, 1)) <= "z") Then
If n = 1 Then
Mid(s, i, 1) = UCase(Mid(s, i, 1))
n = 0
End If
End If
If (Mid(s, i, 1) = ".") Then
n = 1
End If
Next
Cells(1, 2) = s
End Sub

To count number of words in excel

noreply@blogger.com (Manivannan) — Thu, 21 May 2009 15:20:00 +0000

Excel Macro

Suppose you want to count the number of words in paragraph or a sentence
in excel

You just need to do is copy and paste the required text in column 1 on any of the row
and run the below excel macro, you will get the word count on the next column.

Sub src_wdcount()
Dim LR As Long
Dim i, c As Integer
Dim s As String
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
c = 1
s = Cells(i, 1)
l = Len(s)
For j = 1 To l
If Mid(s, j, 1) = " " Then
c = c + 1
End If
Next
Cells(i, 2) = c
Next
End Sub

Viewzi - New Search Experience

noreply@blogger.com (Manivannan) — Thu, 30 Oct 2008 14:13:00 +0000

Viewzi is a whole new way to experience search. Instead of one big list, you get nice visual Views tailored for the content you are looking for:

What is Viewzi?

How is Viewzi Different?

It’s all about Views
We think there are better ways to present information than in a simple list. We start with specific topics or search terms and ask: “Is there a better way to present this data?” The result? Dozens of new, unique ‘views’, or ways to look at information, each custom-tailored for that content. It’s the right data, presented in the right way.

It’s Refined
Some views are more utilitarian and pull from broad sources like Google or Yahoo!, while others can be tailored for a specific topic like world news, celebrity gossip, or gadgets. This means your results are right for your search. No more sifting through millions of results.

It’s Smart
Our View Picker helps connect you with just the right Views. We do the work of finding the best places and sources and brings those together for you. One search bar for hundreds of sources. It’s like having thousands of search engines in one search bar.

It’s Fun
It’s just more fun to use Viewzi. So fun that we’ve been called, ‘The iPhone of search.” Each time you search for something you can try a different View. Our views are hand crafted by some of the best designers and experts across the globe, each one of them passionate about making search exciting and fun for you.

Start Search

Create your own Calendar Using Excel Macros

noreply@blogger.com (Manivannan) — Fri, 05 Sep 2008 13:58:00 +0000

Step 1: Open excel file

Step 2: Press Alt+F11

Step 3: Copy the following macros

Step 4: Save & Execute by pressing Alt+F8

'Accept Year [yyyy] and print the Calender
Sub genyearcal()
Dim r, c As Integer
Dim m, mm, y As Integer
Dim d As Date
Dim months
months = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
Dim rows, cols
rows = Array(1, 1, 10, 10, 19, 19, 28, 28, 37, 37, 46, 46)
cols = Array(1, 9, 1, 9, 1, 9, 1, 9, 1, 9, 1, 9)
calcls
y = CInt(InputBox("Enter Year : "))
For mm = 0 To 11
r = rows(mm)
c = cols(mm)
Cells(r, c) = months(mm) & "-" & y
m = mm + 1
r = r + 1
Cells(r, c) = "Sun"
c = c + 1
Cells(r, c) = "Mon"
c = c + 1
Cells(r, c) = "Tue"
c = c + 1
Cells(r, c) = "Wed"
c = c + 1
Cells(r, c) = "Thu"
c = c + 1
Cells(r, c) = "Fri"
c = c + 1
Cells(r, c) = "Sat"
d = CDate(CStr(m) & "-1-" & CStr(y))
c = DatePart("w", d)
If m Mod 2 = 0 Then
c = c + 8
End If
r = r + 1
For i = 1 To 31
If (y Mod 4 = 0 And m = 2 And i > 29) Then
Exit For
ElseIf ((m = 4 Or m = 6 Or m = 9 Or m = 11) And i > 30) Then
Exit For
ElseIf (y Mod 4 <> 0 And m = 2 And i > 28) Then
Exit For
End If
If m Mod 2 <> 0 Then
If c <= 7 Then
Cells(r, c) = i
Else
c = 1
r = r + 1
Cells(r, c) = i
End If
Else
If c <= 15 Then
Cells(r, c) = i
Else
c = 9
r = r + 1
Cells(r, c) = i
End If
End If
c = c + 1
Next
Next
End Sub

Sub calcls()
Range("a1:o53").Clear
Range("a1:o53").Font.Bold = True
End Sub

Blue Screen of Death (BSOD)

noreply@blogger.com (Manivannan) — Wed, 30 Jul 2008 15:05:00 +0000

Blue Screen of Death very critical problem mainly with windows operating
but we can follow some of the following step to solve this problem.

1. Check with RAM slot, if you have two RAM try with one at time.

2. Try to uninstall newly installed software.

Eg. you may try uninstalling sound, video or Lan drivers etc.

3. If problem continue check with your HDD by rescanning using diagnostic tools like
DM (Seagate) etc.

4. Check the system temperature and Power from CMOS.

I hope this will helpful to resolve your problem

Excel Tutorials and Quiz

noreply@blogger.com (Manivannan) — Fri, 14 Mar 2008 09:34:00 +0000

http://www.writefix.com/ICDL/mod4.htm
http://www.usd.edu/trio/tut/excel/quiz.html
http://www.jegsworks.com/Lessons/numbers/

Business/Money/Entrepreneur/Forex

noreply@blogger.com (Manivannan) — Tue, 12 Feb 2008 11:24:00 +0000

http://coinmill.com/
http://moneycentral.msn.com/Investor/calcs/n_savapp/main.asp
http://www.investorwords.com/
http://www.reliancemoney.com/
http://www.chittorgarh.com/newportal/online-stock-brokers-detail.asp?a=4
http://en.wikipedia.org/wiki/Warren_Buffett
http://www.easy-forex.info/
http://money.rediff.com/money/jsp/browse.jsp
http://www.tnsmallsavings.com/
http://www.licmutual.com
http://www.thehindubusinessline.com/
http://idbipaisabuilder.in/
http://www.niir.org
http://www.ediindia.org/
http://www.mepz.gov.in/home.asp
http://www.sisi-chennai.com/aboutus.asp
http://www.bixee.com/Freelance/jobs-in/Chennai
http://chennai.vivastreet.co.in
http://en.wikipedia.org/wiki/Interest
http://www.outsource2india.com/
http://www.niemglobal.com/
http://sharemarkettechniques.page.tl/
http://www.thehindubusinessline.com/
http://goldprice.in/
http://business.mapsofindia.com/

Pdf Converter

noreply@blogger.com (Manivannan) — Tue, 12 Feb 2008 09:32:00 +0000

Pdf Converter
http://www.primopdf.com/
https://www.pdfonline.com/convert_pdf.asp
http://www.freepdfconvert.com/

C-Language Resources

noreply@blogger.com (Manivannan) — Fri, 29 Jun 2007 13:23:00 +0000

http://www.imada.sdu.dk/~svalle/courses/dm14-2005/mirror/c/
http://www.howstuffworks.com/c.htm
http://www.cs.bu.edu/teaching/c/stack/linked-list/
http://www.cisco.com/univercd/cc/td/doc/product/software/ioss390/ios390sk/skclang.htm
http://www.cs.cf.ac.uk/Dave/C/
http://publications.gbdirect.co.uk/c_book/
http://www.its.strath.ac.uk/courses/c/tableofcontents3_1.html

Windows XP Commands

noreply@blogger.com (Manivannan) — Tue, 26 Jun 2007 10:11:00 +0000

Accessibility Controls = access.cpl
Add Hardware Wizard = hdwwiz.cpl
Add/Remove Programs = appwiz.cpl
Administrative Tools = control admintools
Automatic Updates = wuaucpl.cpl
Bluetooth Transfer Wizard = fsquirt
Calculator = calc
Certificate Manager = certmgr.msc
Character Map = charmap
Check Disk Utility = chkdsk
Clipboard Viewer = clipbrd
Command Prompt = cmd
Component Services = dcomcnfg
Computer Management = compmgmt.msc
Date and Time Properties = timedate.cpl
DDE Shares = ddeshare
Device Manager = devmgmt.msc
Direct X Control Panel (If Installed)* = directx.cpl
Direct X Troubleshooter = dxdiag
Disk Cleanup Utility = cleanmgr
Disk Defragment = dfrg.msc
Disk Management = diskmgmt.msc
Disk Partition Manager = diskpart
Display Properties = control desktop/desk.cpl
Dr. Watson System Troubleshooting Utility = drwtsn32
Driver Verifier Utility = verifier
Event Viewer = eventvwr.msc
File Signature Verification Tool = sigverif
Findfast = findfast.cpl
Folders Properties = control folders
Fonts = control fonts
Fonts Folder = fonts
Free Cell Card Game = freecell
Game Controllers = joy.cpl
Group Policy Editor (XP Prof) = gpedit.msc
Hearts Card Game = mshearts
Iexpress Wizard = iexpress
Indexing Service = ciadv.msc
Internet Properties = inetcpl.cpl
IP Configuration = ipconfig
Java Control Panel (If Installed) = jpicpl32.cpl
Java Application Cache Viewer (If Installed) = javaws
Keyboard Properties = control keyboard
Local Security Settings = secpol.msc
Local Users and Groups = lusrmgr.msc
Logs You Out Of Windows = logoff
Microsoft Chat = winchat
Minesweeper Game = winmine
Mouse Properties = control mouse
Mouse Properties = main.cpl
Network Connections = control netconnections
Network Connections = ncpa.cpl
Network Setup Wizard = netsetup.cpl
Notepad = notepad
Nview Desktop Manager (If Installed) = nvtuicpl.cpl
Object Packager = packager
ODBC Data Source Administrator = z
On Screen Keyboard = osk
Opens AC3 Filter (If Installed) = ac3filter.cpl
Password Properties = password.cpl
Performance Monitor = perfmon.msc
Performance Monitor = perfmon
Phone and Modem Options = telephon.cpl
Power Configuration = powercfg.cpl
Printers and Faxes = control printers
Printers Folder = printers
Private Character Editor = eudcedit
Quicktime (If Installed) = QuickTime.cpl
Regional Settings = intl.cpl
Registry Editor = regedit
Registry Editor = regedit32
Remote Desktop = mstsc
Removable Storage = ntmsmgr.msc
Removable Storage Operator Requests = ntmsoprq.msc
Resultant Set of Policy (XP Prof) = rsop.msc
Scanners and Cameras = sticpl.cpl
Scheduled Tasks = control schedtasks
Security Center = wscui.cpl
Services = services.msc
Shared Folders = fsmgmt.msc
Shuts Down Windows = shutdown
Sounds and Audio = mmsys.cpl
Spider Solitare Card Game = spider
SQL Client Configuration = cliconfg
System Configuration Editor = sysedit
System Configuration Utility = msconfig
System File Checker Utility = sfc
System Properties = sysdm.cpl
Task Manager = taskmgr
Telnet Client = telnet
User Account Management = nusrmgr.cpl
Utility Manager = utilman
Windows Firewall = firewall.cpl
Windows Magnifier = magnify
Windows Management Infrastructure = wmimgmt.msc
Windows System Security Tool = syskey
Windows Update Launches = wupdmgr
Windows XP Tour Wizard = tourstart
Wordpad = write