Excel And VBA Tips

(Column C- Unique data Column A) Column D- Data Picks whose Start With AJAY   (Lookup) Sub Get_Name() Dim i As Integer Set rng1 = Sheet1.Range("g2:g6") Cri1 = Sheet1.Range("H1").Value For Each CELL In rng1 Cri2 = CELL.Value For i = 2 To Sheet1.Range("a65536").End(xlUp).Row Val1 = Left(Cells(i, 2).Value, 4) If Cells(i, 1).Value = Cri2 And Val1 = Cri1 Then Cells(CELL.Row, 8).Value = Cells(i, 2).Value Else End If Next Next End Sub

HLOOKUP function :-  HLOOKUP ( lookup_value , table_array , row_index_num ,range_lookup) 1)  Lookup_value      is the value to be found in the first row of the table. Lookup_value can be a value, a reference, or a text string. 2)  Table_array      is a table of information in which data is looked up. Use a reference to a range or a range name. 3)  Row_index_num      is the row number in table_array from which the matching value will be returned. 4)  Range_lookup      is a logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match  If one is not found, the error value #N/A is returned.

=> One of the best functions in Visual Basic is the Message box . The message box displays a message, optional icon, and selected set of command buttons. The user responds by clicking a button. =>  The statement form of the message box returns no value (it simply displays the box ) :- MsgBox Message, Type, Title where Message      Text message to be displayed Type            Type of message box (discussed in a bit) Title             Text in title bar of message box You have no control over where the message box appears on the screen. =>  The function form of the message box returns an integer value (corresponding to the button clicked by            the user). Example of use (Response is returned value) :- Dim Response as Integer Response = MsgBox (Message, Type, Title) =>  The Type argument is formed by summing four values corresponding to the buttons to display, any icon to show, which button is the default response, and the morality of the message

Repeats a group of statements a specified number of times. Example-1 1) For I = 1 to 50 Step 2 A = I * 2 Debug.Print A Next I Example-2 2) For I = 50 to 1 Step -2 A = I * 2 Debug.Print A Next I In this example, the variable I initializes at 1 and, with each iteration of the For/Next loop, is incremented by 2 (Step). This looping continues until I becomes greater than or equal to its final value (50). If Step is not included, the default value is 1. Negative values of Step are allowed. ·  You may exit a For/Next loop using an Exit For statement. This will transfer program control to the statement following the Next statement.

Data Type                           Suffix Boolean                                None Integer                                    % Long (Integer)                        & Single (Floating)                       ! Double (Floating)                     # Currency                                 @ Date                                   None Object                                None String                                     $ Variant                               None Operator                       Comparison  >                                     Greater than  <                                     Less than  >=                                   Greater than or equal to  <=                                   Less than or equal to  =                                     Equal to <>                                   Not equal to .The result of a comparison operation is a Boolean value ( True or False ). .We will use three logical operators Oper

1. What are database functions e.g. what would be the difference Between SUM ( ) and DSUM ( )? 2. How are array functions useful? 3. What is the syntax for VLOOKUP ( ) / HLOOKUP ( ) function? 4. What functions can be used to lookup data on both the sides of Criteria mentioned (INDEX ( ) / OFFSET (  )) 5. Name any error handling formulas included in Excel built in 6. What is difference between Find and search? 7. What is difference between Delete and Clear Contents? 8. What is difference between Substitute and Replace? 9. What is difference between Count and CountA?

Application (Project) is made up of: =>   Forms - Windows that you create for user interface =>   Controls - Graphical features drawn on forms to allow user interaction (text boxes, labels, scroll bars,                           command buttons, etc.) (Forms and Controls are objects.) =>   Properties - Every characteristic of a form or control is specified by a property. Example properties                                 include names, captions, size, color, position, and contents. Visual Basic applies default                                 properties. You can change properties at design time or run time. =>   Method s - Built-in procedure that can be invoked to impart some action to a particular object. =>   Event Procedures - Code related to some object. This is the code that is executed when a certain                                           event occurs. =>   General Procedures - Code not related to objects. This code must be invoked by the applicati

New Folder name :- Jeet File Name :-  ZTAGGING_SCHEME sub New_folder() MkDir "D:\Jeet" Scheem.SaveAs  "D:\Jeet" & "\ZTAGGING_SCHEME.xlsx" Scheem.Close end sub

Excel Error Messages  Excel lets you know about it with an error message If you create a formula in Excel. A handful of errors can appear in a cell when a formula or function in Excel cannot be resolved. Knowing their meaning helps correct the problem. Error Meaning #DIV/0! Trying to divide by 0 #N/A! A formula or a function inside a formula cannot find the referenced data #NAME? Text in the formula is not recognized #NULL! A space was used in formulas that reference multiple ranges; a comma separates range references #NUM! A formula has invalid numeric data for the type of operation #REF! A reference is invalid #VALUE! The wrong type of operand or function argument is used

AppActivate Activates an application window Beep Sounds a tone via the computer's speaker Call Transfers control to another procedure ChDir Changes the current directory ChDrive Changes the current drive Close Closes a text file Const Declares a constant value Date Sets the current system date Declare Declares a reference to an external procedure in a Dynamic Link Library (DLL) DeleteSetting Deletes a section or key setting from an application's entry in the Windows Registry Dim Declares variables and (optionally) their data types Do-Loop Loops through a set of instructions End Used by itself, exits the program; also used to end a block of statements that begin with If, With, Sub, Function, Property, Type, or Select Erase Re-initializes an array Error Simulates a specific error condition Exit Do Exits a block of Do-Loop code Exit For Exits a block of For-Next code Exit Function Exits a Function procedure Exit Property Exits a property procedure Exit Sub Exi

In Excel, the COUNTIF function is used to count the number of cells in a selected range that meets your requirement in the spreadsheet. The COUNTIF function is typically written in Excel as: =COUNTIF ( Range, Criteria) where "Range" = the group of cells the function is to search. However, in Visual Basic Application, the COUNTIF function is used as below: Sub COUNTIF_FIRSTTOLAST() Dim I As Integer For I = 2 To Sheet1.Range("A" & Rows.Count).End(xlUp).Row Sheet1.Cells(I, 14) = WorksheetFunction.CountIf(Sheet1.Range("M1:M" & I), Sheet1.Cells(I, 13)) Next I End Sub Order Reason Order Reason Running Repair 1 Promo Service 1 Promo Service 2 Paid Service 1 Running Repair 2 Paid Service 2 Promo Service 3 Body Repair 1 Running Repair 3 Running Repair 4