Entries feed    Comments feed

Excel - Spread Sheet

All about Microsoft Excel Tricks and Tips.

Shifting and Moving Column and Row by Grabbing

Grabbing the side of a column, or row (#movecol) below the A-B-C column headers, you will see a line when you move, shift, or copy column(s).  When grabbling the left side of a column it must be below the row headers of the selected column(s) and the pointer arrow should just barely touch the left...

Continue reading


Excel Shortcuts for Cell Formating

MS Excel Formatting Without The Mouse: I thought today might be a good time for another installment of keyboard shortcuts... In MS Excel: Ctrl + Shift + ~ will apply the General Number format to the currently selected cell(s).  Ctrl + Shift + $ will apply the Currency format, with 2 decimal places...

Continue reading


Setting a reference to the VBA Extensibility library

When your coding the VBE you have to set a reference to the VBA Extensibility library. This can be done manualy, but also with the following code. Sub MakeLibrary()      On Error Resume Next                    'if it already exits      ThisWorkbook.VBProject.References _              .AddFromGuid...

Continue reading


Using Shift + F4 key to find Next Occurrence - Excel

Objective: Using Shift + F4 Key to find next occurance How is the Question ?   Step 1. Use the Find option from the Edit menu as normal, specifying what you want to search for and then looking for the first occurrence. Step 2 When the first occurrence is displayed, Press the Esc key (or click on...

Continue reading


Adding description to the User Defined Functions - Excel VBA

One of the new features in Excel 2010 is the ability to provide argument descriptions for user-defined functions. These descriptions appear in Function Arguments dialog box -- which is displayed after you choose a function using the Insert Function dialog box. 1 --------------------------  Sub...

Continue reading


VBA Code - Select Start and Search Text in Text Box

  VBA CODE FOR Select Start in Text Box: You need a 'Form' with 'Two Textbox' Private Sub Form_Load ()    Text1.Text = "Two of the peak human experiences"    Text1.Text = Text1.Text & " are good food and classical music." End Sub Private Sub Form_Click ()    Dim Search, Where...

Continue reading


Precision as Displayed - VERY IMPORTANT

Precision as Displayed: Checking this box will force Excel to calculate to the number of decimal places that appear as a result of your formatting, and will permanently change any numbers stored in cells. You need to be sure you have thought through the full implications of this before using it....

Continue reading


Controlling Excel Calculations

Look at  these options Tools -->Options-->Calculation tab. Calculation Settings Keep Changing Because a number of Excel's calculation settings work at the application level (they are the same for all open workbooks), and are set by the first workbook opened, they may appear to change randomly...

Continue reading


Populating ListBox in VBA - Excel

List Populating for multi-column: ListCount starts at 1 when counting ListBox items or List. But ListBox Indexing of those items or List starts at 0. In your example, you are trying to add an item or List in a Multi-Column ListBox.  Take a look at below example: With me.ListBox1     .Add...

Continue reading


Multiple Conditions to Calculate Total - Count using Array Formula (Simpler Version)

Create a total based on multiple conditions: Use the following formula to calculate the total value of cells F5:F25, where B5:B25 contains "Northwind" and the range C5:C25 contains the region name "Western"....

Continue reading


Enter or Edit the same data on Multiple Worksheets

When you select a group of sheets, changes you make to a selection on the active sheet are reflected in the corresponding cells on all other selected sheets. Data on the other sheets may be replaced.  Select the worksheets where you want to enter data.  How?  Select the cell or cell range where you...

Continue reading


Conditional Sum with More then two Conditions Posted by Aladin Akyurek from Mrexcel.com

COUNT (or COUNTA) is used for counting, SUM for summing when no condition involved. COUNTIF is used for counting, SUMIF for summing when a single condition involved. SUMPRODUCT or an array formula is used for counting as well as for summing when two or more conditions involved. I call these...

Continue reading


Comparing Two Strings - Using VBA strcomp() Function

Comparing two strings for equality can be tricky. The result of var1 = var2 where Var1 and var2 only differ in the use of capitals depends on the Option Compare Setting. This is risky business indeed. Setting Option Compare is a module level decision effecting statement level results. If a module...

Continue reading


Rank Formula in Excel

Using Rank and Countif Function in Excel to know the rank or the position of the Student or the particular detail.

Continue reading


Working WIth Array - Microsoft Excel

On 2002-03-15 16:21, Dave Patton wrote hot use Array to work out Average .

Continue reading