If you find any post useful then, please do share with others. Thanks!

Popular Posts

Contact

Email me

Need help and that also free? I like to learn this way, in case of any question or for a small task, please feel free to email me with details and example data, if required.

Thursday, October 31, 2013

Highlight Row of Selected Cell using VBA

Highlight Row of Selected Cell using VBA

While we are working in a big table of data and comparing with some other data or source, if the selected cell’s row gets highlighted then this can definitely help us in comparing and getting a sense.

Suppose our data is in the range B4:I14. Outside our range somewhere like the cell E17, let us name it as selRow. This is for keeping the details that which row to highlight.

Right click on the sheet name and choose the option of view code. Write the below code there.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    [selRow] = Target.Row
   
End Sub

The next step is to add conditional formatting to highlight selected cell’s row. For this select the entire range (B4:I14) and go to conditional formatting and new rule. Here select the rule type as Use a Formula and use the below rule and use your color formatting.
=ROW(B4) = selRow

It’s done now when you click in a cell of the range the whole row will get highlighted. Please experiment it and let me know in case of any issue.


Stop Duplicate Data Entry through Data Validation

Stop Duplicate Data Entry through Data Validation
As we know Data Validation tool can be used to create a drop down list in a cell and limit the values that user can enter.
But along this we can use Data Validation to stop users from entering duplicate data. Below are the steps for doing so.

Step 1: Select the range i.e. A1:A100 where we want only unique records’ entry and go to Data Validation (Data Tab > Data Validation).
Now select the validation type as Custom so we could use a formula. Now go to the formula field and type the below mentioned Countif formula.

=COUNTIF($A$1:$A$100,A1)<=1



Along this we can also setup the Error Alert for a custom message whenever a duplicate record is typed.



Once it’s setup, it will show like the below.



Wednesday, October 30, 2013

Extract Data by Advanced Filter & VBA

Extract Data by Advanced Filter & VBA

By Using Advanced Filter & VBA, we can filter our data for multiple options in a separate sheet.

Step1: Have you Raw Data (like given below) in a sheet namely RawData and create a table of it by pressing Ctrl L after selecting the data. The default table name would be Table1.
Date
Item
Client
SalesPerson
Monday, November 04, 2013
Mobile
Jim Smith
Peter
Wednesday, November 13, 2013
LapTop
Nadeem
Alyson
Saturday, November 16, 2013
PC
Saeed
Peter
Monday, November 25, 2013
LCD
Jim Smith
Alyson
Tuesday, December 03, 2013
Table
Nadeem
Alyson
Saturday, December 07, 2013
Table
Saeed
Alyson
Wednesday, December 11, 2013
Mobile
Jim Smith
Peter
Sunday, December 15, 2013
LapTop
Nadeem
Alyson
Thursday, December 19, 2013
PC
Saeed
Peter
Monday, December 23, 2013
LCD
Jim Smith
Alyson
Friday, December 27, 2013
Table
Nadeem
Alyson




Step2: In another sheet like in Sheet3 at top have your criteria or options with drop down box.
Suppose against the Item Client in the cell you want in drop down to appear Jim Smith, Nadeem, Saeed and a blank option. For that have your options like in this case the names in a separate sheet. Then select the cell where you want the drop down, go to Data tab and click on Data Validation. On the settings tab in drop down of Allow: select List. In the source provide the range where options items are like if in sheet2’s cells A2:A3 have the client names and cell A4 is blank then go to that sheet and select the range A2:A4. This way, we will be able to show a blank option as well.






Macro to run advanced filter and extract data
First part of the code is to make sure that the current filtered data if any is cleared out before we run the macro again.
The second part of the code is for copying the criteria we select in drop down and paste (special transpose) in the RawData sheet. This is the criteria that our final part of code would be using to filter data.

Put a button and assign this macro to it. For this first save the below code, by going to Developer Tab and then clicking the Visual Basic. A new window will open now click on Insert > Module, paste the below code there save and close the window. Now for putting a button in your sheet (i.e. Sheet 3 for this example) go to Developer tab then Insert then Click on button (Form Control). Place it where you want.
Now right click on it and click on Assign Macro and assign this Filterdata macro to that button. So now after you select your criteria, clicking on this button will run the macro.
One major benefit here is that you can run for all the three options after selecting them or load all data by selecting all options as blank or can extract data for any one or two items as well. Please try it and in case of any issue, please contact me.
Sub FilterData()
    Sheets("Sheet3").Select
    Range("B10").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Clear
   
     Sheets("Sheet3").Select
        Range("A2:B4").Select
    Selection.Copy
    Sheets("RawData").Select
    Range("M1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Sheets("Sheet3").Select   
   
    Sheets("RawData").Range("Table1[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
        Sheets("RawData").Range("M1:O2"), CopyToRange:=Sheets("Sheet3").Range("B10"), Unique:=True
    Columns.AutoFit
    Range("B10").Select
     Sheets("Sheet3").Select
End Sub

Friday, October 25, 2013

Roman & Datedif function

Roman
The Roman function converts a number to Roman format.
=Roman(9) = IX
1
I
2
II
3
III
4
IV
5
V
6
VI
7
VII
8
VIII
9
IX
10
X


Datedif
This function calculates the difference between two dates in a variety of interims like number of years, months or days.
=DATEDIF(Start Date, End Date, Interim)
Here the Start Date must be less than the End Date and interim could be m for months, d for days and y for years.
And the if we are using the interim text directly in the formula then it should be in double quotes

Tuesday, October 8, 2013

VB Code to highlight modified cells

VB Code to highlight modified cells

So In an Excel sheet, you want to highlight if any cell is modified i.e. value changed or deleted then you can do this through the below VB Code.
How to do this?
Open a Workbook
Right click at the bottom of the sheet where sheet name is i.e. Sheet 1 and then Click on View Code.

Paste the below code in the window and save it.

Private Sub WorkSheet_Change(ByVal Target as Range)
Target.Interior.ColorIndex = 6
End Sub


Now test it by changing a value in any cell of that sheet and that particular cell will get highlighted.



Limitations: You can’t know if a cell was modified multiple times and color highlight can also be removed.

Friday, October 4, 2013

Using Index Function & Scroll Bar Button to Make a Dynamic Appointment Box

Using Index Function and Scroll Bar Button to Make a Dynamic Appointment Box

By using the Scroll Bar button and the Index formula, we can prepare the below workaround. The moment we click on the Scroll Bar button the date will change to next and so is the corresponding data like Place and Appointment details. How all this done, is explained here in this post.




The raw data we have is in the below table. For this data create a table by selecting the data and pressing the Ctrl L button. Click somewhere in the data of the table and you will see the Design tab under Table Tools on the main ribbon. Now in the Table name box by typing the table name you can change it to any like in this case it’s Events or you can go with the default name which would be like Table1 or so.

Serial No.
Date
Place
Appointment Details
1
10/1/2013
Islamabad
Meeting with Directors
2
10/2/2013
Peshawar
Site Visit
3
10/3/2013
Gujranwala
Farms Visit
4
10/4/2013
Faisalabad
Fish Farm Visit
5
10/5/2013
Lahore
Attending a Marriage
6
10/6/2013
Sheikupura
Meeting with Factory Managers
7
10/7/2013
Nawabshah
Farms Visit
8
10/8/2013
Hyderabad
Plant Visit
9
10/9/2013
Karachi
Meeting with Directors
10
10/10/2013
Gawadar
Site Visit

Now somewhere in the sheet put the first record number which is 1 and by using the Index function pull the information corresponding to that number which for now is 1 and the info we want to pull is first date, place and Appointment details.
So in the F1 cell we have written 1. So to pull the first date record, our formula would be below.

=INDEX(Events[Date],$F$1)
Here the Events table’s Date column is our array and we want to pull the first record so referring F1 cell where 1 is mentioned.
The result would be like 41548 (date in numbers), so to convert it to date format, use the below formula.
=TEXT(G2,"dddd d, mmmm")
Now similarly pull the Place and Appointment details by same way with Index formula.
=INDEX(Events[Place],F1)
=INDEX(Events[Appointment Details],F1)
Now our results would be like below.
1


Date
41548
Tuesday 1, October
Place

Islamabad
Appointment Details

Meeting with Directors

Go to next sheet or where you want to make that workaround/presentation box.
First Insert the scroll Bar button. For that go to Developer Tab the Insert and press on the Scroll Bar button.






By dragging you can place it anywhere and manage its size as well. Now right click on the button and open the Format Controls and Control tab. As out data range is from 1 to 10 so set the minimum & maximum values to 1 and 10 and incremental change to 1 so on a click the change is of one.  In the Cell Link type the cell reference or first click the cell link box and the cell which you want to refer. This would be the same F1 cell where we put 1 and used it in the Index function to pull the first record. So in this way a click on the button will change the value in F1 cell and accordingly our results of the Index formulas as well.  Now under the button in the cell provide a cell reference of the cell where we have a formula and are pulling the date and then in the next cell the Place and then the Appointment details. Fill colors in there and do some formatting to make it colorful.  Now pressing a button will populate the next date and corresponding details of Place and Appointment.