i am creating a student mark list.bt rank calculation is wrong .plz correct it

name mark1 mark2 total result rank
ram 55 65 120 pass 2
ravi 99 34 133 fail norank
raj 34 75 109 pass 3

my rank calculation formlas is =IF(H11="pass",RANK(G11,G$11:G$13,0),"norank")

plz solve my bplm

INDIRECT function adds realy dynamism to your formulas.

Suppose you want to sum the values in Col A and currently have 10 items (in cells A2 to A11). You can use the following formula to get the result;
=SUM(A2:A11)

As you add more items you need to update the end cell address from A11.

INDIRECT gives you a way to avoid this.

Let us say you have the last cell address in B2...

Update your formula to;
=SUM(INDIRECT("A2:" & B2))

You can have just the row number of the last cell in B2 (eg. 100) then you can use
=SUM(INDIRECT("A2:A" & B2))

This is same as
=SUM(A2:A100)

Though Microsoft Excel is a popular spreadsheet application used by people of all age groups, it is largely used by professionals. Working people or computer users use this application extensively for storing and managing large amount of data. Shortcuts are always better way to save your productive time and Excel users many times require them for performing their work quickly.

Call the following code from Worksheet selection change event. This will highlight the cell for a second and then change back the color of the cell to its original.

Download and try these Templates

1 Gantt Chart Using Stacked Bar Chart

Download the working file here https://www.box.com/s/pwj2rq73zg0f8bg8tcke

Steps to use :

Download the template

Read the instructions on "Config Tab" to update it as per your requirement

2 Gantt Chart Using Formula's and Conditional Formatting

Ever wondered how to insert a delay while running a code.
In this blog I'll explain how to add delay before running a code.

Assumption:
A delay of 25 seconds needs to added before running the code.

Steps:
1. Open a new file
2. Press Alt+F11 to open VBA editor
3. In the left navigation window select ThisWorkbook
4. In the editor window enter the code below
5. Close the VBA editor
6. Save the file as Macro Enabled File
7. Test with you inputs

Code:

Sub InsertDelay()

Dim Delay As Variant
Dim Start As Variant

In this blog we'll see how to open Lotus Notes from Excel VBA.

Steps:
1. Open a new file
2. Press Alt+F11 to open VBA editor
3. In the left navigation window select ThisWorkbook
4. In the editor window enter the code below
5. Close the VBA editor
6. Save the file as Macro Enabled File
7. Test with you inputs

Code:

Sub OpenLotusNotes()

Dim LN As Object

Set LN = CreateObject("Notes.Notessession")
LN.Visible = True

End Sub

I think this has been informative and I thank you for viewing.

-Saurabh

In Microsoft Excel everybody uses If-Then-Else statement every now and then. If statement is useful if you have a limited number of conditions. But let's assume you have to match around 100 conditions, in that case If statement will take a lot of code to be copied and pasted and also to execute.
Instead of using If statement in this scenario you can use CASE statement.

Syntax:

Select Case test_expression
Case condition_1
result_1

Case condition_2
result_2
.
.
.

Case condition_n
result_n

Case Else
result_else

End Select

In this blog, I'll explain how to save the file with cell content as file name.

Assumption:
The Cell Value which is used for file name is A1 and the extension is ".xlsx".

Steps:
1. Open a new file
2. Press Alt+F11 to open VBA editor
3. In the left navigation window select ThisWorkbook
4. In the editor window enter the code below
5. Close the VBA editor
6. Save the file as Macro Enabled File
7. Test with you inputs

Code:

Sub FileNameAsCellContent()

Dim FileName As String
Dim Path As String

Application.DisplayAlerts = False

Let's say you have a tempalate file and you perform a lot of work on that tempalate and accidentaly save that template with all the changes. The original tempalate will be lost and you'll have a lot of work to do.
To avoid this you can save the tempalate file before closing it. But, "to err is human" and what if you forgot to do that.
Well, VBA is here for your rescue.
Using VBA you can ask the user to input the file name every time you close the file.

Assumptions:
User wants to save the file as ".xlsx".

Steps:
1. Open a new file
2. Press Alt+F11 to open VBA editor

Hi,

Here is the three separate macros.
One that provides the status in A1,
One to lock but leave ranges unlocked and
An unlock

The password is set as PASSWORD by default, although to utilise the unlock password you will need to use an input box

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveSheet.ProtectContents = True Then
ActiveSheet.Unprotect "PASSWORD"
Range("A1").Value = "PROTECTED"
ActiveSheet.Protect "PASSWORD"
Else
Range("A1").Value = "NOT PROTECTED"
End If
End Sub

Sub LockSheetCells()

You always add new WorkSheets in your WorkBook. Sometimes it seems very boring to do it manually (specifically if you have multiple sheets to add).
A simple solution to avoide this boredome is to automate it.
I've written the VBA code to add a new WorkSheet at the end of WorkSheets and name it as current Date.

Steps:
1. Open a new file
2. Press Alt+F11 to open VBA editor
3. In the left navigation value select ThisWorkbook
4. In the editor window enter one of the code below (based on your requirement)
5. Close the VBA editor
6. Save the file as Macro Enabled File

CheckBoxes are very common in Excel forms. Based on the condition whether it is checked or not, you may want to run some code.
Now, how would you check whether the CheckBox is checked or not? It is very simple, use the steps below.

Steps:
1. Open a new file
2. Press Alt+F11 to open VBA editor
3. In the left navigation value select ThisWorkbook
4. In the editor window enter the code below
5. Close the VBA editor
6. Save the file as Macro Enabled File
7. Test with you inputs

Code:
Sub CheckBox()
Dim CheckBox As Shape

Flipping data in a row to write it in reverse order.
Let's say you have data in a row and you want to have the data in reverse order.
You can do that in Excel by writing the VBA code.

Assumption: The start position of data is range A1.

Steps:
1. Open a new file
2. Press Alt+F11 to open VBA editor
3. In the left navigation value select ThisWorkbook
4. In the editor window enter the code below
5. Close the VBA editor
6. Save the file as Macro Enabled File
7. Test with you inputs

Code:

Sub FlipDataInARow()
Dim oldStart As Variant
Dim oldEnd As Variant

The below code mails a range of 20 cells, A1 through B10, from the currently active workbook to the e-mail address specified in the code:

Sub EmailRange()

' Select the range of cells on the active worksheet.
ActiveSheet.Range("A1:B10").Select

' Show the envelope on the ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = True

' Set the optional introduction field thats adds
' some header text to the email body. It also sets
' the To and Subject lines. Finally the message
' is sent.
With ActiveSheet.MailEnvelope

In Microsoft Excel, the InStr function finds out if a SubString is present in a String and returns the position of the first occurrence of a SubString in a string.

Syntax:
The syntax for the INSTR function is - InStr( [start], String, Substring, [compare] )

Parameters:
Start (Optional)
Numeric expression that sets the starting position for each search. If omitted, search begins at the first character position. The start index is 1-based.

String (Required)
String expression being searched.

SubString (Required)
String expression sought.

Compare (Optional)

Sub colorCells()
' This macro colors the cell background and changes the font color
' for those cells in Column A which have negative values in Column B

Dim lastRow As Long
Dim colToColor As String
Dim colToTest As String
Dim i As Long
colToColor = "A"
colToTest = "B"
lastRow = Cells(Rows.Count, colToColor).End(xlUp).Row()
For i = 2 To lastRow
If Cells(i, colToTest) < 0 Then
Cells(i, colToColor).Select
With Selection.Interior
.Color = 10040319
End With
With Selection.Font
.Color = -16776961

Let us assume that you have duration of your telephone calls in h:mm:ss format in an Excel sheet;
eg.
00:02:30
00:10:17
1:34:12 ('talking' to your partner after forgetting your anniversary? :-)

Now you want to calculate the cost of each call. Let us assume that phone company charges at $ 0.10 for each 30 second (or fraction thereof) interval.

The following formula (in cell C2) will give you the cost for the time in cell A2 with rate given in cell B2
=ROUNDUP(A2*(24*60*2),0)*$B$2 (Absolute reference to the cell containing the rate will help you copy the formula down)

How do i save my file in CSV comma deliminator with quote
example
"H","FA","8182533815","076902113”,"20080826","0934","312164","67"
"AK","66","PO","A","4","4","4"

 Muchas veces deseamos preguntar al usuario de un libro de Excel un valor para luego utilizarlo en nuetros cálculos

En este simple ejercicio veremos como crear un formulario que pida el nombre del usuario

Este dato será guardado en una celda escogida por nosotros

• Paso a paso con este video...

Hello everyone and I hope you can help me. I am new to this site.

Worksheet change event occurs when value in a cell is changed.
The below code will add the time-stamp in the cell next to the cell whose value is changed.

Use case: This can be used when a store wants to track the orders with order number and time-stamp.

Assumption: Column A will have order number and Column B will be updated with the current time-stamp when the column A is updated.

Steps:
1. Open a new file
2. Press Alt+F11 to open VBA editor
3. In the left navigation value select ThisWorkbook
4. In the editor window enter the code below
5. Close the VBA editor

Use : To add a new text-box using vba

Sub add_textbox_VBA()

Dim shp As Shape
Set shp = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 100, 100, 200, 50) ' add shape
With shp
.TextFrame.Characters.Text = "Example" ' add text to display
.Top = Range("e2").Top ' adjust top
.Left = Range("e2").Left 'adjust left
.TextFrame.AutoSize = True ' turn on autosize
.Fill.ForeColor.RGB = RGB(255, 255, 204) 'choose fill color
.Line.Weight = 1 ' adjust width
.Line.ForeColor.RGB = RGB(255, 0, 18) ' choose color

Use: To change the fill color , border color , etc of all textboxes (shapes) on active sheet

Sub format_all_textboxes_using_vba()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If shp.Type = msoTextBox Then ' choose shape
shp.Fill.ForeColor.RGB = RGB(255, 255, 204) ' choose fill color
shp.Line.Weight = 1 ' adjust width
shp.Line.ForeColor.RGB = RGB(255, 0, 18) ' choose color
shp.Line.DashStyle = msoLineSolid ' choose style
End If
Next

End Sub

Use: To change the color,style,etc of all lines (shapes) on active sheet

Sub format_all_lines_using_vba()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If shp.Type = msoLine Then ' choose shape
shp.Line.Weight = 2 ' adjust width
shp.Line.ForeColor.RGB = RGB(255, 0, 18) ' choose color
shp.Line.DashStyle = msoLineDashDot ' choose dash style to apply
End If
Next

End Sub

Use below lines to display the cell value in a text-box and then re-size the text-box as per the length of text

Sub cellvalue_in_textbox()
' display cell value in textbox
Sheets(1).Shapes("TextBox 1").TextFrame.Characters.Text = Range("b3").Value
' autosize the textbox as per the length of text
Sheets(1).Shapes("TextBox 1").TextFrame.AutoSize = msoAutoSizeTextToFitShape
End Sub

I want to create simple income saving calculator which tells me how much i earn in 6 months,10 Months and annualy in excel for practice...
in short it do a total my income of 6 months 10 months And 12 months and tell me the total of it..
For Example My Monthly Salary Is 1000 Rs
How much i earn in
6 Months?
10 Months?
12 Months?
Please Click On Link To See Example of Calculator I want to creat
http://imageshack.us/scaled/landing/507/ki74.jpg

This macro changes case of selected range. No additional column or UPPER/LOWER/PROPER function necessary.
When you select a range of cells or single cell, run macro and - when prompted - enter "L", "U" or "P" (lower, upper, proper).
Selected cells contents is changed accordingly.

Entering numbers consecutively in same formular cell and want the value saved to another cell without having to copy and paste value only. eg a2=a1*2. enter values in a1 1.2.3 etc and want values saved to other cells.Disd it once and forgot how!! cheers.