Search This Blog

Sunday 19 August 2012

CREATE FUNNEL CHART USING FORMULA


Following are the Steps:

1. Sort your data in Descending Order
2. Enter formula in C2  =REPT("|",B2/50) and drag it down
3.Choose the color that you want.

CHANGE TIME FORMAT FROM "HH:MM Hrs" TO "HH:MM:SS"


Formula in B2:
=TEXT(LEFT(A2,2)/24+MID(A2,4,2)/1440,"hh:mm:ss") and drag it down.


Friday 17 August 2012

FIND AVERAGE OF VALUES EXCLUDING MAX AND MIN VALUE


Formula in A13:
=(SUM(A1:A12)-MIN(A1:A12)-MAX(A1:A12))/(COUNT(A1:A12)-2)

FIND OUT MAXIMUM OCCURED TEXT IN A RANGE



Array formula in B2:
=INDEX($A$2:$A$9,MATCH(MAX(COUNTIF($A$2:$A$9,$A$2:$A$9)),COUNTIF($A$2:$A$9,$A$2:$A$9),0)) with CSE

Monday 13 August 2012

TO FIT THE SIZE OF USERFORM TO YOUR EXCEL WINDOW

Paste the below code in UserForm_Activate procedure and run. It will fit the size of userform to your excel window.


Private Sub UserForm_Activate()
ActiveWindow.WindowState = xlMinimized
With Application
    Me.Top = .Top
    Me.Left = .Left
    Me.Height = .Height
    Me.Width = .Width
End With
End Sub

DATA VALIDATION FOR RESTRICTING DUPLICATE VALUES


Select cell A7 and go to Data Tab, Click on Data Validation, select Custom in Allow field and enter formula in formula field as shown below (Click to enlarge)


Formula:  =ISNA(VLOOKUP(A7,A2:A6,1,FALSE))


Saturday 11 August 2012

ADD ITEMS TO ALL COMBOBOXES OF A USERFORM AT ONE TIME

This is required when you have a number of comboboxes on a userform and you need to add the same list of items to all comboboxes. Instead of adding items one by one to each combobox, you can just use the below code.

Paste this code on userform_intialize event and run.


Private Sub UserForm_Initialize()
Dim nme As Range
  Dim cntrl As Control
  Dim CB As ComboBox
  For Each cntrl In Me.Controls
    If TypeName(cntrl) = "ComboBox" Then
      If CB Is Nothing Then
        For Each nme In Sheet1.Range("MyName")
          cntrl.AddItem nme.Value
          Set CB = cntrl
        Next
      Else
        cntrl.List = CB.List
      End If
    End If
  Next
End Sub

Change highlighted part as per your requirement

ALL SHAPE STYLES

Name Value
msoShape16pointStar 94
msoShape24pointStar 95
msoShape32pointStar 96
msoShape4pointStar 91
msoShape5pointStar 92
msoShape8pointStar 93
msoShapeActionButtonBackorPrevious 129
msoShapeActionButtonBeginning 131
msoShapeActionButtonCustom 125
msoShapeActionButtonDocument 134
msoShapeActionButtonEnd 132
msoShapeActionButtonForwardorNext 130
msoShapeActionButtonHelp 127
msoShapeActionButtonHome 126
msoShapeActionButtonInformation 128
msoShapeActionButtonMovie 136
msoShapeActionButtonReturn 133
msoShapeActionButtonSound 135
msoShapeArc 25
msoShapeBalloon 137
msoShapeBentArrow 41
msoShapeBentUpArrow 44
msoShapeBevel 15
msoShapeBlockArc 20
msoShapeCan 13
msoShapeChevron 52
msoShapeCircularArrow 60
msoShapeCloudCallout 108
msoShapeCross 11
msoShapeCube 14
msoShapeCurvedDownArrow 48
msoShapeCurvedDownRibbon 100
msoShapeCurvedLeftArrow 46
msoShapeCurvedRightArrow 45
msoShapeCurvedUpArrow 47
msoShapeCurvedUpRibbon 99
msoShapeDiamond 4
msoShapeDonut 18
msoShapeDoubleBrace 27
msoShapeDoubleBracket 26
msoShapeDoubleWave 104
msoShapeDownArrow 36
msoShapeDownArrowCallout 56
msoShapeDownRibbon 98
msoShapeExplosion1 89
msoShapeExplosion2 90
msoShapeFlowchartAlternateProcess 62
msoShapeFlowchartCard 75
msoShapeFlowchartCollate 79
msoShapeFlowchartConnector 73
msoShapeFlowchartData 64
msoShapeFlowchartDecision 63
msoShapeFlowchartDelay 84
msoShapeFlowchartDirectAccessStorage 87
msoShapeFlowchartDisplay 88
msoShapeFlowchartDocument 67
msoShapeFlowchartExtract 81
msoShapeFlowchartInternalStorage 66
msoShapeFlowchartMagneticDisk 86
msoShapeFlowchartManualInput 71
msoShapeFlowchartManualOperation 72
msoShapeFlowchartMerge 82
msoShapeFlowchartMultidocument 68
msoShapeFlowchartOffpageConnector 74
msoShapeFlowchartOr 78
msoShapeFlowchartPredefinedProcess 65
msoShapeFlowchartPreparation 70
msoShapeFlowchartProcess 61
msoShapeFlowchartPunchedTape 76
msoShapeFlowchartSequentialAccessStorage 85
msoShapeFlowchartSort 80
msoShapeFlowchartStoredData 83
msoShapeFlowchartSummingJunction 77
msoShapeFlowchartTerminator 69
msoShapeFoldedCorner 16
msoShapeHeart 21
msoShapeHexagon 10
msoShapeHorizontalScroll 102
msoShapeIsoscelesTriangle 7
msoShapeLeftArrow 34
msoShapeLeftArrowCallout 54
msoShapeLeftBrace 31
msoShapeLeftBracket 29
msoShapeLeftRightArrow 37
msoShapeLeftRightArrowCallout 57
msoShapeLeftRightUpArrow 40
msoShapeLeftUpArrow 43
msoShapeLightningBolt 22
msoShapeLineCallout1 109
msoShapeLineCallout1AccentBar 113
msoShapeLineCallout1BorderandAccentBar 121
msoShapeLineCallout1NoBorder 117
msoShapeLineCallout2 110
msoShapeLineCallout2AccentBar 114
msoShapeLineCallout2BorderandAccentBar 122
msoShapeLineCallout2NoBorder 118
msoShapeLineCallout3 111
msoShapeLineCallout3AccentBar 115
msoShapeLineCallout3BorderandAccentBar 123
msoShapeLineCallout3NoBorder 119
msoShapeLineCallout4 112
msoShapeLineCallout4AccentBar 116
msoShapeLineCallout4BorderandAccentBar 124
msoShapeLineCallout4NoBorder 120
msoShapeMixed -2
msoShapeMoon 24
msoShapeNoSymbol 19
msoShapeNotchedRightArrow 50
msoShapeNotPrimitive 138
msoShapeOctagon 6
msoShapeOval 9
msoShapeOvalCallout 107
msoShapeParallelogram 2
msoShapePentagon 51
msoShapePlaque 28
msoShapeQuadArrow 39
msoShapeQuadArrowCallout 59
msoShapeRectangle 1
msoShapeRectangularCallout 105
msoShapeRegularPentagon 12
msoShapeRightArrow 33
msoShapeRightArrowCallout 53
msoShapeRightBrace 32
msoShapeRightBracket 30
msoShapeRightTriangle 8
msoShapeRoundedRectangle 5
msoShapeRoundedRectangularCallout 106
msoShapeSmileyFace 17
msoShapeStripedRightArrow 49
msoShapeSun 23
msoShapeTrapezoid 3
msoShapeUpArrow 35
msoShapeUpArrowCallout 55
msoShapeUpDownArrow 38
msoShapeUpDownArrowCallout 58
msoShapeUpRibbon 97
msoShapeUTurnArrow 42
msoShapeVerticalScroll 101
msoShapeWave 103

CHANGE COMMENT BOX STYLE USING VBA



Paste the below code in the module and run it


Sub Comments_Style()
Dim MyComments As Comment
Dim LArea As Long
For Each MyComments In ActiveSheet.Comments
With MyComments
.Shape.AutoShapeType = msoShapeRoundedRectangle
.Shape.TextFrame.Characters.Font.Name = "Tahoma"
.Shape.TextFrame.Characters.Font.Size = 8
.Shape.TextFrame.Characters.Font.ColorIndex = 2
.Shape.Line.ForeColor.RGB = RGB(0, 0, 0)
.Shape.Line.BackColor.RGB = RGB(255, 255, 255)
.Shape.Fill.Visible = msoTrue
.Shape.Fill.ForeColor.RGB = RGB(58, 82, 184)
.Shape.Fill.OneColorGradient msoGradientDiagonalUp, 1, 0.23
End With
Next
End Sub

Change the shape style, font name, font size, font colour etc. as per your requirement.

To know all shape styles click on:
http://excelvbatipsforbeginners.blogspot.in/2012/08/all-shape-styles.html


Friday 10 August 2012

ENTER DATA ON ALL SHEETS TOGETHER: QUICK TIP

Hold the ctrl key and click on the sheet tabs on which you want to enter the data. Start entering your data and do formatting. Now check the sheets which you have selected. Your data has been entered in all the sheets you have selected with the formatting.

ADD VALUES FROM DIFFERENT SHEETS OF A WORKBOOK WITH A CRITERIA

This is required when we need to add up the values from different sheets to a master sheet with a criteria( Name here).

Example:
Sheet1:
D6: Shweta
E6: 200

Sheet2:
A2: Shweta
B2: 300

Sheet3:
G10: Shweta
H10: 500

Sheet4:
E13: Shweta
F13: Need sum here of all values of "Shweta" from all sheets

Enter formula in F13:
=SUM(SUMIF(INDIRECT("Sheet"&{1,2,3}&"!A:G"),E13,INDIRECT("Sheet"&{1,2,3} &"!B:H")))

Now the question arises what if I have a large number of sheets in my workbook. Say, I need to maintain a different sheet for each day of the month to track the transactions processed by the associates. Hence there are 31 sheets for 31 days of the month.Then it would not be possible to write the sheet number 1,2,3,....31 in the above formula. 

Here is a solution:

Write all the sheet names in a column and give a named range to them. Say MySheets and use below array formula

=SUM(SUMIF(INDIRECT(MySheets & "!A:G"),E13,INDIRECT(MySheets & "!B:H")))
with CSE




WRITE YOUR TEXT IN SHAPES USING VBA

Select a cell (where you want to get the output). Paste the below code in the module and run it.


Sub DrawName()
Const pi = 3.1416
Dim i As Integer
Dim x As Single, y As Single
Dim z As Single
Dim rng As Range
Dim n As Single
Dim k As Integer
Dim sSize As Single
Dim sh As Shape
Dim sName As String
Dim StartLeft As Integer
Dim StartTop As Integer

StartLeft = ActiveCell.Left
StartTop = ActiveCell.Top
sName = InputBox("Enter your text here")
n = 5
k = Len(sName)
sSize = Application.InchesToPoints(0.5)
Randomize Timer
z = 0#
For i = 1 To k
If Mid(sName, i, 1) <> " " Then
x = n * i / k
x = Application.InchesToPoints(x)
If Int(2 * Rnd) = 0 Then
z = z + 0.2
Else
z = z - 0.2
End If
y = Application.InchesToPoints(z)
Set sh = ActiveSheet.Shapes.AddShape _
(msoShapeSun, StartLeft + x, StartTop + y, sSize, sSize)

sh.Fill.ForeColor.RGB = RGB(255, 0, 0)
sh.Fill.Visible = msoTrue
sh.TextFrame.Characters.Text = Mid(sName, i, 1)
sh.TextFrame.Characters.Font.Size = 12
sh.TextFrame.Characters.Font.Name = "Arial"
sh.TextFrame.Characters.Font.Bold = True
sh.TextFrame.Characters.Font.Color = vbBlack
End If
Next i
End Sub

Change the highlighted part to change the shape type. To know all shape types click on:
http://excelvbatipsforbeginners.blogspot.in/2012/08/all-shape-styles.html

After running this code, an inputbox will appear. Enter your text in the inputbox and press enter. Result would be like: (See the image below)


Tuesday 7 August 2012

MOVING TITLE FOR USERFORM


Click on the video to see how it will look like.


Follow the below steps to create a moving title for your userform:

1. Launch Microsoft Excel
2. Press ALT+F11 to open Visual Basic Editor
3. Insert an userform
4. Put a label control on your userform ( say, label 1) and clear the text (delete the text under caption in properties)
5.Put another label (say, label 2) above label 1and write your text in label 2( I wrote "EXCEL VBA TIPS").

See the below image



6. Double click on any userform control, code window will appear.
7. Select "General" as object and paste the below code there as shown in the below image(Click to enlarge)



Code:

Private Sub MoveTitle()
    On Error Resume Next
Again:
    Label2.Left = Label2.Left - 0.05
    DoEvents
    For j = 1 To 50000: Next
    If Label2.Left + Label2.Width < 0 Then Label2.Left = Me.Width
    GoTo Again
End Sub

8. Now select "Userform" as object and "Activate" as procedure and paste the below code there as shown in below image.(Click to enlarge)


Code:

Private Sub UserForm_Activate()
Call MoveTitle
End Sub

Run the userform and your userform title will start moving as shown in the above video.

Did this post help you? Please post your valuable comment. Thanks!!


Sunday 5 August 2012

CHANGE TO SENTENCE CASE


Formula in B2:  =UPPER(LEFT(A2,1))&MID(LOWER(A2),2,999) and drag it down.

Did it help you? Please post your valuable comment. Thanks!!

Saturday 4 August 2012

CREATE IN-CELL BAR CHART IN EXCEL 2007 USING FORMULA- TYPE 2



Enter formula in C4  =REPT("|",B4) and drag it down


Reduce font size to 7 and choose any colour and your chart is ready.

To See Type 1, Click on
http://excelvbatipsforbeginners.blogspot.in/2012/07/create-in-cell-bar-chart-in-excel-2007.html

Did it help you? Please post your valuable comment. Thanks!!


SPLIT CELL CHARACTERS USING VBA



Paste the below code in the module, select the cell you want to split and run the code.


Sub split_cell_char()
Dim i As Integer
For i = 1 To Len(ActiveCell)
ActiveCell.Offset(0, i) = VBA.Mid(ActiveCell, i, 1)
Next
ActiveSheet.UsedRange.Columns.AutoFit
End Sub

Did it help you? Please post your valuable comment. Thanks!!

Friday 3 August 2012

SUM ALL DIGITS OF A CELL USING VBA


Paste the below UDF(User Defined Function) in module and use function.
Function Name  "=sum_cell_values"

Function sum_cell_values(rng As Range)
Dim i As Integer
j = 0
For i = 1 To Len(rng)
j = WorksheetFunction.Sum(j, VBA.Mid(rng, i, 1))
Next
sum_cell_values = j
End Function

Did it help you? Please post your valuable comment. Thanks!

SUM UNIQUE VALUES IN A RANGE



Array formula in B2:

=SUM(IF(FREQUENCY($A$2:$A$10,$A$2:$A$10),$A$2:$A$10))


with CSE

Is this post helpful to you? Please post your valuable comment. Thanks!