Blog Archive
Thursday, February 23, 2012
K-CET 2012 Marks list and rank
Click on the link to download the PG-KCET 2012 marks and rank list. Rank is only indicative of the range actual might differ. You can enlarge and download the file or visit http://www.4shared.com/office/ETD0tkWt/PgCET_2012.html for excel download..
Tuesday, March 10, 2009
Part 2: Counting cells based on interior color of the cell & Value
In the previous post we discussed about counting cells based on color now there is extension to include color and values both or only color.
= CountColorCode(RangeArea As Range, Criteria_Cell As Range, Optional Consider_Value As Boolean = False)
Criteria_Cell is Range where we need to look for colored cells and count themcRange is the criteria range, which needs to be searched
Consider_Value is bollen True/False if left alone default is False and value of cell is not taken in consideration
= CountColorCode(RangeArea As Range, Criteria_Cell As Range, Optional Consider_Value As Boolean = False)
Criteria_Cell is Range where we need to look for colored cells and count themcRange is the criteria range, which needs to be searched
Consider_Value is bollen True/False if left alone default is False and value of cell is not taken in consideration
Download the file for codes and example
<
Saturday, March 7, 2009
Counting cells based on interior color of the cell
Paste the code in vba standerd module in Excel:
Public Function CountColorCode(rng As Range, cRange As Range) As Integer
Dim I As Integer
For Each Cell In rng
If Cell.Interior.ColorIndex = cRange.Interior.ColorIndex Then
I = I + 1
End If
Next
CountColorCode = I
End Function
------------Testing the function-----
Syntax rng As Range = CountColorCode(rng, cRange)
Rng is Range where we need to look for colored cells and count them
cRange s the criteria range which needs to be searched
eg: we need to count the cells in range “A1:B20” which color same as cell D1
Then the formula is = CountColorCode(A1:B20, D1)
Public Function CountColorCode(rng As Range, cRange As Range) As Integer
Dim I As Integer
For Each Cell In rng
If Cell.Interior.ColorIndex = cRange.Interior.ColorIndex Then
I = I + 1
End If
Next
CountColorCode = I
End Function
------------Testing the function-----
Syntax rng As Range = CountColorCode(rng, cRange)
Rng is Range where we need to look for colored cells and count them
cRange s the criteria range which needs to be searched
eg: we need to count the cells in range “A1:B20” which color same as cell D1
Then the formula is = CountColorCode(A1:B20, D1)
Thursday, March 5, 2009
Transpose a multidimensional table into a two dimensional table
Pivot’s are very effective tool in Excel but only if we have data stored in rolling format; where as most of people store data in running tables making it useless for pivots, and often there are times when we need to append this data to some database which makes it critical.
The codes below will help you use a Custom VBA Excel Function to transpose a running table into rolling table the syntax is TransArryaSize(strtRange As Range, cOffset As Variant, endRange As Range)
Paste the code below in standard VBA module try this macro to test the fuction
Sub TestConvertToTranspose()
Call TransArryaSize(Range("E11"), 2, Range("J37"))
' E11 is first cell of table
' 2 are the first two column that will remain as it is
' J37 is last cell of table
End Sub
"************Paste it from hear on in any VBA module***********
Function TransArryaSize(strtRange As Range, cOffset As Variant, endRange As Range)
' Trnaspose Function by Ritwik @ http://ritwik-shukla-vb.blogspot.com
Dim RowCount, ColumnCount As Integer
RowCount = endRange.Row - strtRange.Row
ColumnCount = endRange.Column - strtRange.Column + 1
Range(strtRange.Offset(1, 0), Cells(endRange.Row, strtRange.Column + cOffset - 1)).Copy
Sheets.Add
Range("A2").Select
ActiveSheet.Name = "Trans"
For I = 1 To ColumnCount - cOffset
Sheets("Trans").Cells(65536, 1).Select
Selection.End(xlUp).Offset(1, 0).Select
Call copyColumn
Next I
Application.CutCopyMode = False
Sheets("Data").Activate
Sheets("Data").Cells(strtRange.Row, strtRange.Column + cOffset).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Trans").Activate
For I = 1 To RowCount
Sheets("Trans").Cells(65536, (cOffset + 1)).Select
Selection.End(xlUp).Offset(1, 0).Select
Call copyRow
Next I
Application.CutCopyMode = False
For N = 1 To RowCount
Sheets("Data").Activate
Sheets("Data").Cells(strtRange.Row + N, strtRange.Column + cOffset).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Trans").Activate
Sheets("Trans").Cells(65536, (cOffset + 2)).Select
Selection.End(xlUp).Offset(1, 0).Select
Call copyRow
Next N
End Function
Function copyColumn()
'
' To Copy Column data by mulitiplying
' 3/5/2009 by Ritwik
'
'
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Function
Function copyRow()
'
' To Copy Row Data into Transpose from Row
' 3/5/2009 by Ritwik
'
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
End Function
The codes below will help you use a Custom VBA Excel Function to transpose a running table into rolling table the syntax is TransArryaSize(strtRange As Range, cOffset As Variant, endRange As Range)
Paste the code below in standard VBA module try this macro to test the fuction
Sub TestConvertToTranspose()
Call TransArryaSize(Range("E11"), 2, Range("J37"))
' E11 is first cell of table
' 2 are the first two column that will remain as it is
' J37 is last cell of table
End Sub
"************Paste it from hear on in any VBA module***********
Function TransArryaSize(strtRange As Range, cOffset As Variant, endRange As Range)
' Trnaspose Function by Ritwik @ http://ritwik-shukla-vb.blogspot.com
Dim RowCount, ColumnCount As Integer
RowCount = endRange.Row - strtRange.Row
ColumnCount = endRange.Column - strtRange.Column + 1
Range(strtRange.Offset(1, 0), Cells(endRange.Row, strtRange.Column + cOffset - 1)).Copy
Sheets.Add
Range("A2").Select
ActiveSheet.Name = "Trans"
For I = 1 To ColumnCount - cOffset
Sheets("Trans").Cells(65536, 1).Select
Selection.End(xlUp).Offset(1, 0).Select
Call copyColumn
Next I
Application.CutCopyMode = False
Sheets("Data").Activate
Sheets("Data").Cells(strtRange.Row, strtRange.Column + cOffset).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Trans").Activate
For I = 1 To RowCount
Sheets("Trans").Cells(65536, (cOffset + 1)).Select
Selection.End(xlUp).Offset(1, 0).Select
Call copyRow
Next I
Application.CutCopyMode = False
For N = 1 To RowCount
Sheets("Data").Activate
Sheets("Data").Cells(strtRange.Row + N, strtRange.Column + cOffset).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Trans").Activate
Sheets("Trans").Cells(65536, (cOffset + 2)).Select
Selection.End(xlUp).Offset(1, 0).Select
Call copyRow
Next N
End Function
Function copyColumn()
'
' To Copy Column data by mulitiplying
' 3/5/2009 by Ritwik
'
'
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Function
Function copyRow()
'
' To Copy Row Data into Transpose from Row
' 3/5/2009 by Ritwik
'
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
End Function
Wednesday, June 4, 2008
Convert number into Indian National Rupees word!
MS Excel is a banker’s delight software; they have every function and formula to carryout day to day task. But if you are in India and you are required to process a check copy with INR in words, the only way of doing it is typing it yourself.
There are articles about creating a UDF to do this using VBA but unfortunately most of them are about USD number system. Below is Excel Add-in that will provide you a function called =ConvertCurrencyToINR() to convert any number in INR words.
Download and Save the .xla file from link, then use Tool-> Add-in in Excel to install the add-in
Formula Syntax;
=ConvertCurrencyToINR(myNumber As Number, check_negetrive as Boolean)
ConvertCurrencyToINR() checks if the number is negative (only if you check_negetrive as True or 1) and then convert the number is INR words and Prefix Negative if it’s a negative value. You can disable the check_negetrive option by provide 0 or False input in formula.
How to use
In Cell A1 enter -15896.36
In cell A2, inter the formula =ConvertCurrencyToINR(A1,1)
In Cell B2 inter the Fomula =ConvertCurrencyToINR(A1, 0)
There are articles about creating a UDF to do this using VBA but unfortunately most of them are about USD number system. Below is Excel Add-in that will provide you a function called =ConvertCurrencyToINR() to convert any number in INR words.
Download and Save the .xla file from link, then use Tool-> Add-in in Excel to install the add-in
Formula Syntax;
=ConvertCurrencyToINR(myNumber As Number, check_negetrive as Boolean)
ConvertCurrencyToINR() checks if the number is negative (only if you check_negetrive as True or 1) and then convert the number is INR words and Prefix Negative if it’s a negative value. You can disable the check_negetrive option by provide 0 or False input in formula.
How to use
In Cell A1 enter -15896.36
In cell A2, inter the formula =ConvertCurrencyToINR(A1,1)
In Cell B2 inter the Fomula =ConvertCurrencyToINR(A1, 0)
Labels:
Excel UDF,
MS Excel Add-in,
Number to Words,
Password VBA
Excel-add in for "Collecting Data from Multiple Excel Workbooks into a Single Text File using VBA"
This is a Excel Add-in created with cods I had discussed in "Collecting Data from Multiple Excel Workbooks into a Single Text File using VBScript"
Labels:
MS Excel,
MS Excel Add-in,
Password VBA,
Vbscript
Monday, May 26, 2008
Prompting for a password before running a VBA code
The macro below will help you set a custom password for running a macro in workbook. Let’s say you have a run query button on your worksheet and you want to make sure before running the codes behind this query user needs to be authenticated.
Private Sub CommandButton1_Click()
Dim strPass As String ' This sting is password
Dim lCount As Long
For lCount = 1 To 3
strPass = InputBox(Prompt:="Password Please", Title:="PASSWORD REQUIRED")
If strPass = vbNullString Then 'Cancelled
sLast.Select
Exit Sub
ElseIf strPass <> "Password" Then 'InCorrect password
MsgBox "Password incorrect", vbCritical, "Failed"
Else 'Correct Password
Exit For
End If
Next lCount
If lCount = 4 Then 'They use up their 3 attempts
MsgBox "The password is wrong, workbook is going close", vbInformation
ActiveWorkbook.Close SaveChanges:=False
Exit Sub
Else
MsgBox "Correct Password", vbOKOnly 'put your codes from hear on
End If
End Sub
Private Sub CommandButton1_Click()
Dim strPass As String ' This sting is password
Dim lCount As Long
For lCount = 1 To 3
strPass = InputBox(Prompt:="Password Please", Title:="PASSWORD REQUIRED")
If strPass = vbNullString Then 'Cancelled
sLast.Select
Exit Sub
ElseIf strPass <> "Password" Then 'InCorrect password
MsgBox "Password incorrect", vbCritical, "Failed"
Else 'Correct Password
Exit For
End If
Next lCount
If lCount = 4 Then 'They use up their 3 attempts
MsgBox "The password is wrong, workbook is going close", vbInformation
ActiveWorkbook.Close SaveChanges:=False
Exit Sub
Else
MsgBox "Correct Password", vbOKOnly 'put your codes from hear on
End If
End Sub
Subscribe to:
Posts (Atom)