Working on (this probably relates to the problem): Microsoft Excel for Mac, Version 15.41 (171205) macOS High Sierra, Version 10.13.2 In order to avoid code duplication and make it more readable I tried to define my own function that locates the cell containing a column name. It has 3 arguments: headerName - the name of the column (ex. 'ID', 'Price', etc.) sheetName - the name of the sheet to be searched in rowNum - the number of the row where the headers of the columns are located This is what I wrote: Function findCell(headerName, sheetName, rowNum) Set cell = _ Sheets(sheetName).Rows(rowNum).Find(headerName,LookIn:=xlValues, _ Lookat:=xlWhole, SearchOrder:=xlRows, SearchDirection:=xlNext, _ MatchCase:=True) findCell = cell.Column End Function To be exact - I want it to return the cell and not the cell's column, I'm just using the column as a way to verify correctness. However this doesn't work with function (returns #VALUE!) but for some reason it does work with sub (outputting with msgbox).

If you want to master Excel keyboard shortcuts on a Mac, you need to take a moment to understand how the Mac keyboard is arranged, and how it can be configured through system preferences. This is especially important with Excel, which uses a number of function keys for shortcuts. How to use the Excel FIND function to Get the location of text in a string.

For me the following code works Function findCell(ByVal headerName As String, ByVal sheetName As String, ByVal rowNum As Long) As Long Dim cell As Range Set cell = _ Sheets(sheetName).Rows(rowNum).Find(headerName, LookIn:=xlValues, _ Lookat:=xlWhole, SearchOrder:=xlRows, SearchDirection:=xlNext, _ MatchCase:=True) If cell Is Nothing Then findCell = 0 Else findCell = cell.Column End If End Function EDIT: Just a wild guess because above function works in a windows environment. Function findCell(ByVal headerName As String, ByVal sheetName As String, _ ByVal rowNum As Long) As Long Dim cell As Range Dim ws As Worksheet Dim rg As Range Set ws = Sheets(sheetName) Set rg = ws.Rows(rowNum) ' Set cell = _ ' rg.Find(headerName, LookIn:=xlValues, _ ' Lookat:=xlWhole, SearchOrder:=xlRows, SearchDirection:=xlNext, _ ' MatchCase:=False) Set cell = rg.Find(headerName,, xlValues, xlWhole, xlRows, xlNext, True) If cell Is Nothing Then findCell = 0 Else findCell = cell.Column End If End Function EDIT2: Another wild guess Set cell = rg.Find(headerName,, &HFFFFEFBD, 1, 1, 1, True). I hope this may help you. I use this code to find a cell with the value 'IB' in the field of cells that are selected. Once it finds 'IB', I change the value of the cell to 'BS'. Cut that column, and insert it in another location. How to open microsoft visual basic for applications outlook in mac download.

Then it iterates I and goes onto the next search. I am sure it's not elegant, but it might help you to get unstuck: Cells.Select Selection.Find(What:='IB', after:=ActiveCell, LookIn:=xlFormulas, lookat _:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Select Selection.Value = 'BS' MyCol = ActiveCell.Column Columns(MyCol).Cut Columns(i).Insert Shift:=xlToRight i = i + 1. Testing it with another Mac on an older version of macOS and older version Microsoft Excel for Mac resulted the same (even with @Storax variants). Testing the code I posted on PC/Windows will actually work (as mentioned in the comments by others). This left me to conclude that this issue is a weird bug in VBA version of Mac. My workaround - instead of defining a function I defined a sub that uses a global variable. The sub is adapted from @Storax better code: Public findColOut As Long Sub findCol(ByVal headerName As String, ByVal sheetName As String, ByVal rowNum As Long) Dim cell As Range Set cell = Sheets(sheetName).Rows(rowNum).Find(headerName,_ LookIn:=xlValues, Lookat:=xlWhole, SearchOrder:=xlRows,_ SearchDirection:=xlNext, MatchCase:=True) If cell Is Nothing Then findColOut = 0 Else findColOut = cell.Column End If End Sub.