2018-09-21 14:57
IN-CELL PROGRESS METER IN EXCELColumn A - Employee NameColumn B - TargetColumn C - Achieved ScoreColumn D - Achieved % ( Derived Field )Step 1: Write formulaPut the below formula in cell D2… Read More
Blog Directory > Education Blogs > Languages Blogs > Excel VBA Tips languages Blog >
Excel Vba Tips Blog
excelvbatipsforbeginners.blogspot.in
Tags:
weekday
column
reverse
function
integerif keycode vbkeyreturn
rank
count
rank function
pivot tablestep
shift
This is a blog regarding excel and VBA tips. It contains posts on excel formulas, quick tips and VBA code on various topics.
2017-12-17 10:51
I have a column "Product Category" in my dataset having 23 entries including duplicates.I want to know the distinct number of categories. There are different ways of doing it. In this post… Read More
2017-12-10 14:07
This post is dedicated on handling the dates in VBA by using some in-built functions. I have explained some functions in short, used to work with dates.Assigning a datevalue to a variable.My… Read More
2017-10-26 11:40
1) Write the below code on the initialize event of your userformPrivate Sub UserForm_Initialize()Me.ChildBox1.Enabled = FalseMe.ChildBox2.Enabled = FalseEnd SubIt makes the Child-1 &am&hell…Read More
2017-09-15 13:19
Here I want to retrieve values for Data1, Data2, Data3 & Data4 from the given dataset (A1:F10) based on two lookup values that is EmpCode and Dept.Formula used in range C13:F13=VLO… Read More
2017-09-01 11:20
We have a in-built RANK function in excel to assign rank to our data but the issue with this function is it skips ranking(s) if there is a tie. In above case, it assigns 3 to 40 and 5 to 38… Read More
2016-09-15 14:58
I explored a lot on internet for a customized formula which can reverse the digits in a cell and found many solutions using VBA user defined function or by installing add-in but couldn't fin… Read More
2016-08-16 08:23
Below is the code to make next textbox visible once you finish typing in first textbox and press enter key or tab.Controls on my form:4 labels: &nbs&hell…Read More
2016-08-15 09:45
Cell D2 : Data validation for Month NameCell E2: Data validation for Weekday NameFormula in F2:=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR(D2),MONTH(D2),1)&":"&EOMONTH(D2,0))))=MAT… Read More
2014-08-17 16:18
Say, you are given shift timings ( shift start time and shift end time) and you need to calculate shift hours from them. Below is the formula for that.Formula in B2:=MOD(RIGHT(A2,5)-LEFT(A2… Read More
2012-09-14 13:06
Use below codes to restrict the users to move your userform.1) Declare variablesPrivate m_sngAnchorLeft As SinglePrivate m_sngAnchorTop As SinglePrivate m_blnSetAnchor As Boolean2) Past… Read More
2012-09-14 12:59
To disable the close(X) button of userform paste the below code on the QueryClose event of your userform.Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)If CloseMode… Read More
2012-08-19 12:48
Following are the Steps:1. Sort your data in Descending Order2. Enter formula in C2 =REPT("|",B2/50) and drag it down3.Choose the color that you want Read More
2012-08-19 12:26
Formula in B2:=TEXT(LEFT(A2,2)/24+MID(A2,4,2)/1440,"hh:mm:ss") and drag it down Read More
2012-08-17 08:06
Formula in A13:=(SUM(A1:A12)-MIN(A1:A12)-MAX(A1:A12))/(COUNT(A1:A12)-2) Read More
2012-08-17 08:04
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 Read More
2012-08-14 10:31
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 Excel2. Press ALT+F11 to open Visual Basic Editor3. Inse… Read More
2012-08-14 06:01
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 = xlMinimizedWith A… Read More
2012-08-13 13:26
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… Read More
2012-08-11 17:06
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… Read More
2012-08-11 16:50
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.1416Dim i As IntegerDim x As Single, y As SingleDim z As SingleDim… Read More
2012-08-11 16:48
Paste the below code in the module and run itSub Comments_Style()Dim MyComments As CommentDim LArea As LongFor Each MyComments In ActiveSheet.CommentsWith MyComments.Shape.AutoShapeType = ms… Read More
2012-08-11 16:46
Name Value msoShape16pointStar 94 msoShape24pointStar 95 msoShape32pointStar 96 msoShape4pointStar 91 msoShape5pointStar 92 msoShape8pointStar 93 msoShapeActionButtonBackorPrevious 1… Read More
2012-08-10 14:52
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 bee… Read More
2012-08-10 14:29
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: ShwetaE6: 200Sheet2:A2: ShwetaB2: 300Sheet3:G10: Shw… Read More
2012-08-05 07:00
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!! Read More
2012-08-04 08:51
Enter formula in C4 =REPT("|",B4) and drag it downReduce font size to 7 and choose any colour and your chart is ready.To See Type 1, Click onhttp://excelvbatipsforbeginners.blogspot.in… Read More
2012-08-04 08:22
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 IntegerFor i = 1 To Len(ActiveCell)ActiveCell.Offset(0, i) = VBA.Mid(Acti… Read More
2012-08-03 14:27
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 Integerj = 0For i = 1 To Len(rng)j… Read More
2012-08-03 13:05
Array formula in B2:=SUM(IF(FREQUENCY($A$2:$A$10,$A$2:$A$10),$A$2:$A$10))with CSEIs this post helpful to you? Please post your valuable comment. Thanks! Read More
2012-07-31 08:10
Say your data is in the range A2:A6 and you want to create a data validation list using this data in C1. It is easy to do. You will go to Data-- Data Validation--List--Select Range--Pr… Read More
2012-07-31 07:13
Below are the steps1. Click on MS Office button2. Click on Excel Option3. Go to Customize4. Select All Commands5. Click on Camera and then Add6. Click OK(Click to Enlarge) Read More
Subscribe to Excel Vba Tips
Get updates delivered right to your inbox!