All you want to know about PNB Scam

Posted from "The Hindu" dated 28.02.2018

Wise Words- 4

Two impressive and thought-provoking dialogues from the movie “Vikram Vedha”

·      Dei… Vidhi, vidhi nu aayiram thadavai sollitiru.. Life chance-a eduthutu vandhu “sedhu.. indha pidinu kaila kodukadhu”. Unnaku onnu thevaina, adhukaga try panama irukradhudhan thappu.

·   Eppayum prachanai-na prachanai ennanu paarkadha.. Prachanaikaana kaaranam ennanu paaru.

Wise Words- 3

·             Have the courage to follow your heart and intuition. They somehow know what you truly want to become

·         Don’t be trapped by dogma – which is living with the results of other people’s thinking.

·         Your time is limited, so don’t waste it living someone else’s life.

·             If you don’t love something, you’re not going to go the extra mile, work the extra weekend, challenge the status quo as much

VBA in Excel - Properties, Methods and Events - 2

1.

Selection.SpecialCells(xlCellTypeBlanks).Select

This statement selects the blank special cells of the original selection. (The word SpecialCells is a method that handles many of the options in the Go To Special dialog box.)

2. 

Selection.FormulaR1C1 = "=R[-1]C"

This statement assigns =R[-1]C as the formula for the entire selection. When you entered the formula, the formula you saw was =C2, not =R[-1]C. The formula =C2 really means "get the value from the cell just above me," but only if the active cell happens to be cell C3. The formula =R[-1]C also means "get the value from the cell just above me," but without regard for which cell is active.

You could change this statement to Selection.Formula = "=C2" and the macro would work exactly the same—provided that the order file you use when you run the macro is identical to the order file you used when you recorded the macro and that the active cell happens to be cell C3 when the macro runs. However, if the command that selects blanks produces a different active cell, the revised macro will fail. The macro recorder uses R1C1 notation so that your macro will always work correctly.

3. 

Selection.CurrentRegion.Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, _

    Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

Range("A1").Select

These statements select the current region, convert the formulas to values, cancel copy mode, and select cell A1.

4.

Sub AddDates()

    Range("A1").Select

    Selection.EntireColumn.Insert

    ActiveCell.FormulaR1C1 = "Date"

    Range("A2").Select

    Selection.CurrentRegion.Select

    Selection.SpecialCells(xlCellTypeBlanks).Select

    Selection.FormulaR1C1 = "Nov-2007"

    Range("A1").Select

End Sub

This macro is pretty straightforward. Notice that the statement that enters the word Date uses the word ActiveCell as the object, changing the "formula" of only the active cell, whereas the statement that enters the actual date uses the word Selection as the object, changing the "formula" of the entire range of selected cells. When you enter a formula using the Enter key alone, the macro uses the word ActiveCell. When you enter a formula using Ctrl+Enter, the macro uses the word Selection. (If the selection consists of only a single cell, ActiveCell and Selection are equivalent.)

In addition, using just the Enter key changes the selection to the next cell down. That's why the Range("A2").Select statement is in the macro. It doesn't hurt anything, but it is also unnecessary. Removing unnecessary statements from a recorded macro makes it easier to read, and easier to modify in the future if you ever need to.

5. 

Selection.End(xlDown).Select

This statement is equivalent to pressing Ctrl+Down Arrow. It starts with the active cell, searches down to the last nonblank cell, and selects that cell.

6.

 Workbooks.Open Filename:="C:\MSP\ExcelVBA07SBS\Orders.xlsx"

In the Visual Basic editor window, the statement that opens the master list should be highlighted:

Workbooks.Open Filename:="C:\MSP\ExcelVBA07SBS\Orders.xlsx"

This statement opens the master list workbook.

TIP

If you remove the path from the file name, leaving only the actual file, the macro looks for the file in the current folder. That would be useful if you move the project to a new folder. However, if the master list is always in the same location, but the source file may be in different locations, it is better to leave the full path of the master file.

7.

ActiveWorkbook.Close SaveChanges:=False

The SaveChanges argument answers the dialog box's question before it even gets asked. While testing, you can have the macro not save the workbook. Once you're ready to really use the macro, you can change the argument to True.

8.

ActiveWindow.SelectedSheets.Delete

The statement refers to the "selected sheets of the active window" because it's possible to select and delete multiple sheets at the same time. (Press and hold the Ctrl key as you click several sheet tabs to see how you can select multiple sheets. Then click an unselected sheet without using the Ctrl key to deselect the sheets.) Because you're deleting only one sheet, you could change the statement to ActiveSheet.Delete if you wanted, but that isn't necessary.

9.

Application.DisplayAlerts = False

DisplayAlerts is a property of the Excel application. When you set the value of this property to False, any confirmation prompts that you would normally see are treated as if you had selected the default answer. The DisplayAlerts setting lasts only until the macro finishes running, so you don't need to set it back to True. You do, however, need to be careful to never run this macro when the active sheet is something you care about. Naturally, you should also be careful to save your work often and keep backup copies.

(From the book

Microsoft® Office Excel® 2007 Visual Basic® for Applications Step by Step 

by Reed Jacobson)

VBA in Excel - Properties, Methods and Events - 1

       One of the best way to learn programing is to learn from the already written codes. As I start to learn VBA for Excel, I feel it extremely difficult to put together the arguments and form a clear knowledge base. The exercise of putting together the  myriad arguments and syntax for programming appears like solving a jigsaw  puzzle.

    In this Excel VBA series, I will posting my learnings of the commonly used properties, methods and events.

   Thanks to my gurus Professor Charlie Nuttelman of  University of collorado and Lokesh Jayasankar sir of BNP Paribas for being of kind guidance in helping me to learn.

    These are basically sample codings. Explanation is appended to codings wherever I felt they are necessary.

1)   msgbox range("d22")

2)   Range("a3:aa5000").AutoFilter Field:=5, Criteria1:="OS-sal"

3)   range("G:G").select

4)   sales = WorksheetFunction.Sum(Range("J:J"))

Range("a1") = sales

5) Displays the color index for the color in the cell

   msgbox range("A1").interior.colorindex

6)   Application.workbooks("Project6).Worksheets("Main").Range(B16")

7)   range("a3").offset(1000,1000)

8)   Copy data from one worksheet and paste into another worksheet

Worksheets("Commands").Range("A1").copy

Worksheets("Feuil3").select

Range("A1").select

Activesheet.paste

9)

Sub Example9( )

Dim DIV as double

Sheet1.Select

ActiveSheet.Range("A8:S60000").autofilter field:=5, Criteria1:="DIV"

With wksT.autofilter.Range

Range("J" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select

Range(Selection, Selection.End(xlDown)).Select

DIV = Application.WorksheetFunction.Sum(Selection)

End With

Range("P8:P" & Cells(Rows.Count, "B").End(xlUp).Row)

End sub

10)  msgbox(" the square is "&y&" only.")

11)  msgbox(" the square is "& formatnumber(y,2)&" only.")

12)  Get the value in active cell

Activecell = formatnumber (y,2)

13)   Get the value from cell A1 and output the value to cell C3

Input

x = range("a1")

Output

range("c3")=formatnumber(y,2)

​

14)  Msgbox range("b2:d3").count * activecell + cells(5,2)* inputbox("Please enter a number:")

15)  set w1=worksheets("sheet1)

16)  Pick a cell from existing selection

Selection. Cells(2,2)

17)  Count the number of rows and columns in a selection

nr= selection.rows.count

nc=selection.columns.count

18)  x = inputbox()

19)Get a value of 5 into a cell some rows/columns from activecells

  activecell.offset(2,2) = 5

20)  sub example20()

dim x ,y ,z

x=inputbox()

y=activecell

z= x+y

activecell.offset(2,2) = z

End sub

TED Talk by Sam Berns

Sampson Gordon "Sam" Berns (October 23, 1996 – January 10, 2014) was an American teen who had progeria and helped raise awareness about the disease. He was the subject of the HBO documentary Life According to Sam, which was first screened in January 2013. He died one year later, after appearing in a TEDx Talks video titled "My philosophy for a happy life."

Be Ok with what you can't do (now), because there is so much you can do (now)

  

Wise Words - 2

My thought: And let the goal be selfless