It’s a known issue. The VBA Round() function uses Banker’s rounding while the spreadsheet cell function uses arithmetic rounding. Check the details here: PRB: Round Function different in VBA 6 and Excel Spreadsheet The workaround proposed by Microsoft is to write a custom function to get the desired results. Banker’s rounding always rounds 0.5 to … Read more
Several years too late: Just for completeness I want to give yet another answer: First, go to Excel-Options -> Formulas and enable R1C1 references. Then use =CELL(“width”, RC) RC always refers the current Row, current Column, i.e. “this cell”. Rick Teachey’s solution is basically a tweak to make the same possible in A1 reference style … Read more
Be careful, the VBA Round function uses Banker’s rounding, where it rounds .5 to an even number, like so: Round (12.55, 1) would return 12.6 (rounds up) Round (12.65, 1) would return 12.6 (rounds down) Round (12.75, 1) would return 12.8 (rounds up) Whereas the Excel Worksheet Function Round, always rounds .5 up. I’ve done … Read more
You don’t need VBA for this but still if you want VBA Solution then you can go with any of the other two answers. 🙂 We can use Excel formula to find the Font Color of a cell. See this example. We will be using XL4 macros. Open the Name Manager Give a name. Say … Read more
There may be a more eloquent way, but this is the way I came up with: The function to find the last populated cell in a column is: =INDEX( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ; ROWS( FILTER( A:A ; NOT( ISBLANK( A:A ) ) ) ) ) So if you combine … Read more
As commented, just in case the link I posted there broke, try this: Add a Name(any valid name) in Excel’s Name Manager under Formula tab in the Ribbon. Then assign a formula using GET.CELL function. =GET.CELL(63,INDIRECT(“rc”,FALSE)) 63 stands for backcolor. Let’s say we name it Background so in any cell with color type: =Background Result: … Read more
Application.WorksheetFunction.Index(array, row, column) If you specify a zero value for row or column, then you’ll get the entire column or row that is specified. Example: Application.WorksheetFunction.Index(array, 0, 3) This will give you the entire 3rd column. If you specify both row and column as non-zero, then you’ll get only the specific element. There is no … Read more
Using following simple formula is much faster =LOOKUP(2,1/(A:A<>””),A:A) For Excel 2003: =LOOKUP(2,1/(A1:A65535<>””),A1:A65535) It gives you following advantages: it’s not array formula it’s not volatile formula Explanation: (A:A<>””) returns array {TRUE,TRUE,..,FALSE,..} 1/(A:A<>””) modifies this array to {1,1,..,#DIV/0!,..}. Since LOOKUP expects sorted array in ascending order, and taking into account that if the LOOKUP function can not … Read more
Evaluate might suit: http://www.mrexcel.com/forum/showthread.php?t=62067 Function Eval(Ref As String) Application.Volatile Eval = Evaluate(Ref) End Function