Naming a range in Excel for Mac One of the more useful features in Excel that I see a lot of end users overlook is Named Ranges in Excel. A named range is a cell or group of cells that is given a descriptive name. That name can then be used in a formula in place of the cell coordinates. To define a Named Range, select the range of cells you would like to name. This can be one cell, a range of cells, a column or row.
Note: These instructions apply to Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 for Mac, Excel 2016 for Mac, Excel for Mac 2011, Excel for Office 365, and Excel Online. About Array Formulas. You can use the Frequency function within Excel in Microsoft Office 2011 for the Macintosh platform to calculate the frequency of data that lies within a certain range.
Click the Name Box in the upper left hand corner of the worksheet, right above the column A. Type the name you would like to call this range and hit enter. By giving cell A1 the name of SalesTax, a formula can now be created in any other Excel cell like this: =B1.
![Free Free](/uploads/1/2/5/5/125511601/124808368.png)
![Arrays Arrays](http://www.pivot-table.com/wp-content/uploads/2014/03/stoprefresh02.png)
SalesTax, this formula will multiply the value in B1 by the value in the cell named SalesTax. A named range can also be a group of cells. Let’s say you have sales figures in Column C and Rows 10 through 25. Select the range C10:25. Again click the Name Box and type JanuarySales. Now in cell C26 if you type the formula =sum(JanuarySales), you will get a total of the named range.
One of the other useful features of names ranges is as a navigation tool. Once you have ranges defined, simply typing the name in the Name Box and hitting enter will take you directly to that named range.
This is very useful if you want to quickly jump to a particular cell within a workbook with many worksheets or within a very large worksheet itself. To remove a Named Range choose Insert Name Define. Select the Name you would like to delete and press the Delete Button.
I'm working on making an existing macro-enabled spreadsheet functional on Excel for Mac 2011. I have a function that searches arrays for a specified value: Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean IsInArray = (UBound(Filter(arr, stringToBeFound)) -1) End Function It works perfectly in Excel 2013, but on Excel for Mac 2011, I receive the error: Runtime error '9': Subscript out of range I broke it apart and found that the UBound call is what's causing the error. I'd like to change as little as possible for maintainability.
How can I fix this error for the Mac version? Thanks in advance for any replies! Edit: @Siddharth Rout's solution is spot on, but since I was searching arrays within a loop, I had to modify the loop to reset the array between each iteration as follows (in case anyone else runs into the same issue!): ' - START Reset Array for OS X - Dim OSXHack(99) As String For intIndex = 0 To 99 OSXHack(intIndex) = Original(intIndex) Next Erase Original ReDim Original(0 To 99) As String For intIndex = 0 To 99 Original(intIndex) = OSXHack(intIndex) Next Erase OSXHack ' - END Reset Array for OS X. Ok This is my observation. If you call the function once in a procedure then it will work fine. For Example Sub Sample Dim a As Variant Dim s As String Dim strTemp As String s = 'CC' strTemp = 'A,B,C,D' a = Split(strTemp, ',') Debug.Print IsInArray(s, a) End Sub Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean IsInArray = (UBound(Filter(arr, stringToBeFound)) -1) End Function However if you call it twice in the procedure then you will get an error Runtime error '9': Subscript out of range. Maybe it is an Excel 2011 Bug?
Sub Sample Dim a As Variant Dim s As String Dim strTemp As String s = 'CC' strTemp = 'A,B,C,D' a = Split(strTemp, ',') Debug.Print IsInArray(s, a) s = 'A' Debug.Print IsInArray(s, a) End Sub Solution Recreate the array. See this example. Sub Sample Dim a As Variant Dim s As String Dim strTemp As String s = 'CC' strTemp = 'A,B,C,D' a = Split(strTemp, ',') Debug.Print IsInArray(s, a) s = 'A' a = Split(strTemp, ',') Debug.Print IsInArray(s, a) End Sub Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean IsInArray = (UBound(Filter(arr, stringToBeFound)) -1) End Function.