Problem 1 :- Filter Data and paste to other sheet

Dear Sir / madam,

Please see the attachment.  

I have a problem I want to copy data base on the Person name which i highlighted with yellow colour.

suppose if the person name "JKM" a sheet will created with the name "JKM" and the entire rows data relating to Jkm WILL copy to the new sheets of JKM.  if the person name is HM the the same rule will be follow.

Attachment

1. Filter Data and Make new Sheet with name




Sub Test()
Dim J As Integer
Dim sh As Worksheet
Sheet1.Range("M:M").Copy Sheet1.Range("Z:Z")
Sheet1.Range("Z:Z").RemoveDuplicates Columns:=1, Header:=xlNo
'Set sh = Worksheets
For J = Sheet1.Range("Z" & Rows.Count).End(xlUp).Row To 3 Step -1

    ActiveSheet.Range("A2:M" & Sheet1.Range("M" & Rows.Count).End(xlUp).Row).AutoFilter Field:=13, Criteria1:=Sheet1.Cells(J, 26)


Sheet1.Range("A2:M" & Sheet1.Range("M" & Rows.Count).End(xlUp).Row).Select
Selection.SpecialCells(xlCellTypeVisible).Copy
Set sh = Worksheets.Add
sh.Range("A1").PasteSpecial xlPasteAll
sh.Name = Sheet1.Cells(J, 26)
Sheet1.Activate
ActiveSheet.Range("A2:M" & Sheet1.Range("M" & Rows.Count).End(xlUp).Row).AutoFilter
Next J
Sheet1.Range("Z:Z").Delete

MsgBox "Thanks"

End Sub

2. Delete Old Sheet and Make new worksheet with updated Data

Sub Test()
Dim ws As Worksheet
Dim J As Integer
Dim sh As Worksheet
Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error Resume Next
Sheet1.Range("M:M").Copy Sheet1.Range("Z:Z")
Sheet1.Range("Z:Z").RemoveDuplicates Columns:=1, Header:=xlNo
'Set sh = Worksheets

For Each ws In Application.ActiveWorkbook.Worksheets
        If ws.Name <> "Sheet1" Then
            ws.Delete
        End If
    Next

For J = Sheet1.Range("Z" & Rows.Count).End(xlUp).Row To 3 Step -1
Sheet1.Range("A2:M" & Sheet1.Range("M" & Rows.Count).End(xlUp).Row).AutoFilter Field:=13, Criteria1:=Sheet1.Cells(J, 26)
Sheet1.Range("A2:M" & Sheet1.Range("M" & Rows.Count).End(xlUp).Row).Select
Selection.SpecialCells(xlCellTypeVisible).Copy
Set sh = Worksheets.Add
sh.Range("A1").PasteSpecial xlPasteAll
sh.Name = Sheet1.Cells(J, 26)
Sheet1.Activate
ActiveSheet.Range("A2:M" & Sheet1.Range("M" & Rows.Count).End(xlUp).Row).AutoFilter
Next J
Sheet1.Range("Z:Z").Delete
Sheet1.Range("A1").Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True

MsgBox "Thanks"

End Sub

End Function in Excel

This function tests two or more conditions to see if they are all true.

It can be used to test that a series of numbers meet certain conditions.

It can be used to test that a number or a date falls between an upper and lower limit.

Note :- 

1. Returns TRUE if all its arguments evaluate to TRUE.

2. Returns FALSE if one or more arguments evaluate to FALSE

=And(Logical1,Logical2,.......)

=AND(TRUE,TRUE)       =      TRUE
=AND(TRUE,FALSE)      =      FALSE

Age Calculation

You can calculate age based on their birthday and today's date.

1 :- Datedif Function :-  Calculates the number of days, months, or years between two dates

                            =datedif(start date, End Date, Unit)

Unit:-

1. Number of completed year :- Y

2. Number of completed Month :- M

3. Number of completed Days :- D

4. The difference between the days. The months and years of the dates are ignored :- MD

5. The difference between the months. The days and years of the dates are ignored :- YM

6. The difference between the days. The years of the dates are ignored :- YD

2 :- Today Date Function :- Returns the current date.

                           =today()

Age Calculation :- (C1 = 10-Mar-1980)

Number of Years :-  DATEDIF(C1,TODAY(),"y")

Number of Months:-DATEDIF(C1,TODAY(),"ym")

Number of Day's :- DATEDIF(C1,TODAY(),"md")

Birth date :	10-Mar-1980
Number of Years :	37
Number of Months :	2
Number of Days :	1

Filename formula

There may be times when you need to insert the name of the current workbook or worksheet in to a cell.

 =CELL("filename")

The problem with this is that it gives the complete path including drive letter and folders.

Pick the Excel Path.

 =MID(CELL("filename"),1,FIND("[",CELL("filename"))-1)

Pick the Workbook name.

 =MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)

Pick the Worksheet name.

 =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

Time Calculation

1. :- Excel can work with time very easily.

2:- Time can be entered in various different formats and calculations performed.

3:- There are one or two oddities, but nothing which should put you off working with it.

Time Formats :- HH:MM:SS

When time is entered into worksheet it should be entered with a colon between the Hour, Minute and Seconds.

Example 1 :- 10:20:22 , 13:30:01, 23:00:00

                            OR

Example 2 :- 10:20 AM , 01:30 PM, 11:00 PM

Excel can either the 24 hour or the am/pm system.

Difference between two times

End Time - Start Time  = difference

You may need to reformat the answer.

Adding time

End Time + Start Time  = Answer

=Sum(start Time + End Time)

How To Apply Custom Formatting

 1. Click on the cell which needs the format.

2. Choose the Format menu.

3. Choose Cells.

4. Click the Number tag at the top right.

5. Choose Custom.

6. Click inside the Type: box.

7. Type [hh]:mm as the format.

8. Click Enter or OK to confirm.

Return the characters after Nth character in a Cell

Return the characters after the nth ","|"."

Use of SUBSTITUTE function when you are replacing text based on its content.

SUBSTITUTE find and replaces your old text with new text in a text string.

SUBSTITUTE(text, old_text, new_text, [instance_number]) The SUBSTITUTE function syntax has the following arguments Text  Required. The text or the reference to a cell containing text for which you want to substitute characters. Old text  Required. The text you want to replace. New text  Required. The text you want to replace old text with. Instance number  Optional. Specifies which occurrence of old text you want to replace with new text. If you specify instance number, only that instance of old text is replaced. Otherwise, every occurrence of old text in text is changed to new text.

REPT :- Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string.

REPT(text, numbertimes)

Text  Required. The text you want to repeat.
Number times  Required. A positive number specifying the number of times to repeat text.

TRIM :-

TRIM(text)

Text  Required. The text from which you want spaces removed.

Return the characters after 10th character 

=TRIM(RIGHT(SUBSTITUTE(G2,"|",REPT(" ",11255),10),11255))

=TRIM(RIGHT(SUBSTITUTE(G3,".",REPT(" ",11255),10),11255))

=TRIM(RIGHT(SUBSTITUTE(G4,",",REPT(" ",11255),10),11255))

If Function with Example

IF function is one of the most valuable functions in Excel. you can make logical comparisons.

IF(Something is True, then do something, otherwise do something else)

IF Examples :- 

If value is Equals to "RAM" Then return "OK" otherwise return "Not Ok"

=IF(A1="RAM","OK","Not Ok")

If value is Grater then to 5 Then return "OK" otherwise return "Not Ok"

=IF(A4>5,"OK","Not Ok")

If F1 is grater then 3 then (F1*2) cell value is multiple 2 and the condition of first condition false result is 0

=IF(F1>3,F1*2,0)

Microsoft Excel Basic

*The  Max function will return the largest (max) value in the selected range. 

*The Min function will display the smallest value in a selected set of cells.

*The SUM function adds all the numbers that are contained in cells.

*The COUNT function counts the number of cells that contain numbers

*The COUNTA function counts the number of cells that are not empty in a range.

Get_Name wise

(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 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.

The Message Box In VBA

=> 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 box.

=>  The first component of the Type value specifies the buttons to display :-
Value     Meaning                                                  Symbolic Constant

0        OK button only                                            vbOKOnly
1       OK/Cancel buttons                                        vbOKCancel
2       Abort/Retry/Ignore buttons                             vbAbortRetryIgnore
3       Yes/No/Cancel buttons                                  vbYesNoCancel
4       Yes/No buttons                                              vbYesNo
5       Retry/Cancel buttons                                      vbRetryCancel

Using the For/Next loop

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 theFor/Next loop, is incremented by 2 (Step). This looping continues until I becomesgreater than or equal to its final value (50). If Step is not included, the default valueis 1. Negative values of Step are allowed.
·  You may exit a For/Next loop using an Exit For statement. This will transferprogram control to the statement following the Next statement.

Visual Basic Data Types

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
Operator                       Operation
Not                                Logical not
And                               Logical and
Or                                 Logical or

·  The Not operator simply negates an operand.
·  The And operator returns a True if both operands are True. Else, it returns a False.
·  The Or operator returns a True if either of its operands is True, else it returns a False.
·  Logical operators follow arithmetic operators in precedence.

Application (Project) is made up

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.
=>  Methods - 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 application.
=>  Modules - Collection of general procedures, variable declarations, and constant definitions used by                                      application.

MS EXCEL INTERVIEW QUESTION

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?

With the help of VBA Create new folder and Save file

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

MS Excel Error Messages

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

Common Visual Basic for Application (VBA) Statements

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	Exits a subroutine procedure
FileCopy	Copies a file
For Each-Next	Loops through a set of instructions for each member of a series
For-Next	Loops through a set of instructions a specific number of times
Function	Declares the name and arguments for a Function procedure
Get	Reads data from a text file
GoSub...Return	Branches to and returns from a procedure
GoTo	Branches to a specified statement within a procedure
If-Then-Else	Processes statements conditionally
Input #	Reads data from a sequential text file
Kill	Deletes a file from a disk
Let	Assigns the value of an expression to a variable or property
Line Input #	Reads a line of data from a sequential text file
Load	Loads an object but doesn't show it
Lock...Unlock	Controls access to a text file
Mid	Replaces characters in a string with other characters
MkDir	Creates a new directory
Name	Renames a file or directory
On Error	Gives specific instructions for what to do in the case of an error
On...GoSub	Branches, based on a condition
On...GoTo	Branches, based on a condition
Open	Opens a text file
Option Base	Changes the default lower limit for arrays
Option Compare	Declares the default comparison mode when comparing strings
Option Explicit	Forces declaration of all variables in a module
Option Private	Indicates that an entire module is Private
Print #	Writes data to a sequential file
Private	Declares a local array or variable
Property Get	Declares the name and arguments of a Property Get procedure
Property Let	Declares the name and arguments of a Property Let procedure
Property Set	Declares the name and arguments of a Property Set procedure
Public	Declares a public array or variable
Put	Writes a variable to a text file
RaiseEvent	Fires a user-defined event
Randomize	Initializes the random number generator
ReDim	Changes the dimensions of an array
Rem	Specifies a line of comments (same as an apostrophe ['])
Reset	Closes all open text files
Resume	Resumes execution when an error-handling routine finishes
RmDir	Removes an empty directory
SaveSetting	Saves or creates an application entry in the Windows Registry
Seek	Sets the position for the next access in a text file
Select Case	Processes statements conditionally
SendKeys	Sends keystrokes to the active window
Set	Assigns an object reference to a variable or property
SetAttr	Changes attribute information for a file
Static	Declares variables at the procedure level so that the variables retain their values as long as the code is running
Stop	Pauses the program
Sub	Declares the name and arguments of a Sub procedure
Time	Sets the system time
Type	Defines a custom data type
Unload	Removes an object from memory
While...Wend	Loops through a set of instructions as long as a certain condition remains true
Width #	Sets the output line width of a text file
With	Sets a series of properties for an object
Write #	Writes data to a sequential text file

How To Use COUNTIF in Visual Basic Application

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

    

17 ways to Optimize VBA Code for FASTER Macros

1. Analyze the Logic 

2. Turn off ScreenUpdating
3. Turn off 'Automatic Calculations'
4. Disable Events
5. Hide Page breaks
6. Use 'WITH' statement
7. Use vbNullString instead of ""
8. Release memory of Object variables
9. Reduce the number of lines using colon(:)
10. Prefer constants
11. Avoid Unnecessary Copy and Paste
12. Clear the Clipboard after Paste
13. Avoid 'Macro Recorder' style code.
14. Use 'For Each' than 'Indexed For'
15. Use 'Early Binding' rather 'Late Binding'
16. Avoid using Variant
17. Use Worksheet Functions wherever applicable