Excel

Delete all pictures in a selected range of cells Excel VBA

Delete all pictures in a selected range of cells Excel VBA The following program Deletes all pictures in a selected range of cells Excel VBA. Sub DeletePics() Dim PicRng As Range Dim Pic As Picture Dim Rng As Range Application.ScreenUpdating = False Set Rng = Range("B1:B100") For Each Pic In ActiveSheet.Pictures Set PicRng =...

Create new CSV file with copied data using Excel VBA

Create new CSV file with copied data using Excel VBA Create New CSV file with copied data  using Excel VBA from a macro file. Besides that,  the data transfer takes place to csv with copy paste program codes. Therefore, the following program generates csv file with copied data. Sub csv_gen() Dim wb_main As Workbook...

Lookup and return multiple matches in Excel

Lookup and return multiple matches in Excel Formula to return multiple matches. It is an array formula. Press Shift+Ctrl+Enter in the formula bar. =IFERROR(INDEX($A$1:$B$10,SMALL(IF($A$1:$A$10="Excel",ROW($A$1:$A$10)),ROW(A1)),2),"") =IFERROR(INDEX(Sheet1!B$2:B$6,SMALL(IF(Sheet1!$A$2:$A$6="A",ROW(Sheet1!$A$2:$A$6)),ROW(1:1))-1),"") =SUMPRODUCT((--MID($A$2:$A$4,FIND("#",$A$2:$A$4)+1,10))) It uses Index, Small and Row functions to perform the required. Excel Factor 17 Lookup and Return Multiple Matches    

Convert Excel Table to Normal Range VBA

Convert Excel Table to Normal Range VBA Here is the vba code to convert Excel Table to Normal Range without manual operation. ActiveSheet.ListObjects(1).Unlist Expecting that, there is only one table exists in the sheet.   http://vba.relief.jp/excel-vba-convert-table-to-range-of-cells/

OR logic in Sumproduct function in Excel

OR logic in Sumproduct function in Excel Sumproduct usually will be applied with AND logic. OR logic in sumproduct function can be implemented as detailed below. A) AND condition example is as follows: =Sumproduct((A3:A159=B3:B159)*(D3:D159="Target A")) =Sumproduct((A3:A159=B3:B159)*(D3:D159="Target B")) B) There are ways to add OR condition using “+” symbol. The “plus sign” (+) is for OR in...

Display Times as Hours/Minutes in Excel

Display Times as Hours/Minutes in Excel Usually, Excel stores time as decimal values and displaying times as hours or minutes. Ex: A1=200 B1=A1/1440 (1440 = 24*60) = 0.1389 Now, use custom format as h:mm (or) h” hours and “m” minutes” There is an another option in Excel using Convert function.  =Convert(A1,”mn”,”hr”) -Ref: https://www.accountingweb.com/technology/excel/display-times-as-hoursminutes-in-excel

Minimum if multiple criteria using Excel

Minimum if multiple criteria using Excel Get the minimum value by checking multiple criteria from different columns. Minimum if multiple criteria is calculated with the following array formulas including nested if conditions. A) The following formula checks different criteria and get the minimum values from value column. =MIN(IF(rng1=criteria1,IF(rng2=criteria2,values))) Press Ctrl+Shift+Enter to convert to array...

Check, if a cell is in the list of ranges or not in Excel?

Check, if a cell is in the list of ranges or not in Excel? There are ways to search a text in range of cells using countif formula. The range can be specified using named ranges / specifying the ranges directly / using array formats. Here are the following examples that lists different ways...

Presence of number in a string using Excel Formulas

Presence of number in a string using Excel Formulas Use Find to check each occurrence of number in the array and count at the end. If the value is greater than 0, then it can be concluded that, cell B1 has number in it. The result is TRUE or FALSE. =COUNT(FIND({0,1,2,3,4,5,6,7,8,9},B1))>0 Few examples are...

Published On

November 2018
M T W T F S S
« Oct    
 1234
567891011
12131415161718
19202122232425
2627282930  

This is a demo store for testing purposes — no orders shall be fulfilled. Dismiss