 # Excel VBA. FREE Excel VBA Code & Excel Macro Help Related:  ExcelMS Office ExcelExcel

Microsoft Excel Tips - Excel.Tips.Net Find a set of amounts that match a target value There are instances when one wants to figure out which elements of a set of numbers (amounts) total to a target value. One instance where this may be necessary is for a company that receives a check for outstanding accounts receivable (A/R) bills but doesn't have matching documentation indicating what bills are being paid. Excel template using Solver VBA code to find multiple matching combinations References For a shareware product that addresses the same issue, please visit TM Match Target. Recent Comments Excel template using Solver The Excel template uses Solver to provide one possible combination of numbers that total to the target amount. The Excel template VBA code to find multiple matching combinations There have been several requests for solutions that provide a list of all possible matches. Also, the code may take a fair amount of time to run depending on the size of the list of numbers being searched. How to use the code The second cell contains the target value. The code References

Excel: Find closest value | Get Digital Help Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with a criterion Find closest value Question: How to find closest number in a list? Answer: Array formula in cell C2: How to create an array formula Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.Press and hold Ctrl + Shift.Press Enter onceRelease all keys Explaining array formula Step 1 - Return absolute values of numbers in an array, numbers without its sign ABS(number) returns the absolute value of a number, a number without its sign. becomes and returns Step 2 - Return the smallest number MIN(number1,[number2]) returns the smallest number in a set of values. returns 1. Step 3 - Return the relative position of an item in an array MATCH(lookup_value;lookup_array; [match_type]) returns the relative position of an item in an array that matches a specified value returns 12. and returns 42. EDIT: Added excel workbook to this blog post Download excel example file

Custom Number Formats (Multiply & Divide by any Power of 10) Posted on January 31st, 2012 in Excel Howtos , Huis , Posts by Hui - 35 comments In the past here at Chandoo.org and at many many other sites, people have asked the question “How can I display a number Multiplied or Divided by 10, 100, 1000, 1000000 etc, but still have the cell maintain the original number for use in subsequent calculations“. Typically the answer has been limited to “It can’t be done” or “It can only be done in multiples of 1000”. Well thanks to a tip I picked up from Kyle who responded to a post here at Chandoo.org they are all wrong. It is possible to Multiply or Divide any cell contents by any power of 10 using Custom Number Formats ! That is: How does this work: When using custom number format we have two possibilities to modify the display number Use a Comma to divide by 1000; orUse a % to Multiply by 100 So using a combination of these any power of 10 can be obtained. So using the correct combination of , and % can result in any power of 10 multiplier we require. Downloads

Using Microsoft Excel Worksheet Functions in Visual Basic [Excel 2003 VBA Language Reference] The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location. Note Some worksheet functions aren't useful in Visual Basic. For example, the Concatenate function isn't needed because in Visual Basic you can use the & operator to join multiple text values. Calling a Worksheet Function from Visual Basic In Visual Basic, the Microsoft Excel worksheet functions are available through the WorksheetFunction object. The following Sub procedure uses the Min worksheet function to determine the smallest value in a range of cells. Sub UseFunction() Dim myRange As Range Set myRange = Worksheets("Sheet1").Range("A1:C10") answer = Application.WorksheetFunction.Min(myRange) MsgBox answer End Sub If you use a worksheet function that requires a range reference as an argument, you must specify a Range object. Note Visual Basic functions don't use the WorksheetFunction qualifier. Example

Match two criteria and return multiple rows in excel | Get Digital Help Question: I have a table of 3 coloms (Security name, date, price) and I have to find the price a a security at a certain date in a table that contain many securities and prices for this securities for different dates. If I work with vlookup or Index-match I got only the first price for a certain securities. So I am not able to find the price of a securities that match both the name of the securities and the date. Could you advice if there is any way to overcome? Answer: Here are two possible solutions: Create an excel 2007 tableUse an array formula The easiest is the excel 2007 table. Excel 2007 table Select the range Click "Insert" tabClick "Table" Click OK Click "black triangle" on Security header.Select one or more securitiesClick "black triangle" on Date header.Select one or more dates Array formula Array formula in F9: =INDEX(tbl, SMALL(IF(COUNTIF(\$G\$3, \$B\$3:\$B\$19)*COUNTIF(\$G\$4, \$C\$3:\$C\$19), ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)), COLUMN(A1)) + CTRL + SHIFT + ENTER. How to create an array formula