Making .find command compatible with Excel 2011 (Mac)

**jaywalkar** · 04-11-2013, 08:23 AM

I am sure there is a very simple solution to this problem but I have had no luck in finding an answer.

Essentially, I have a macro that performs a .find function on a sheet and loops through all the cells on that sheet. An example is shown below;

For Each c In Rng
        FoundValQuant = Sheets("Cans").Cells.Find(What:=c.Value, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Offset(-1, 3).Value
        FoundValPrice = Sheets("Cans").Cells.Find(What:=c.Value, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=True, SearchFormat:=False).Offset(-1, 1).Value
        FoundValCol = Sheets("Cans").Cells.Find(What:=c.Value, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Offset(-1, 1).Interior.Color
        c.Offset(0, -1).Value = FoundValQuant
        c.Offset(0, 4).Value = FoundValPrice
        c.Interior.Color = FoundValCol
        c.Offset(0, -1).Interior.Color = FoundValCol
        c.Offset(0, 1).Interior.Color = FoundValCol
        c.Offset(0, 4).Interior.Color = FoundValCol
    Next c

My problem is as follows. In my windows version of excel (Excel 2013) it works flawlessly. When used with a Mac version (Excel 2011) the code does not return the values from the .find command and returns the interior color as black rather than the color that it should actually find and identify.

As far as I can see, this part of the code in my workbook seems to be the only one deciding not to work so I assume that the .find command must work somewhat differently in Excel 2011.

The only thing I have done that may have an impact here is I have disabled the 'Microsoft VBScript Regular Expressions 5.5' reference through the VBE as when the workbook is opened in Excel 2011 it is listed as 'Missing' which was causing my code to error.

This wouldn't bother me too much if I hadn't spent my whole Easter weekend building a workbook for a friend who owns a Mac. So now I just need to iron out this last crease to complete my work.

Thanks in advance.

**JosephP** · 04-11-2013, 08:52 AM

I'm surprised that code runs in 2011-there is no SearchFormat argument in 2011

it also appears you are performing the same find 3 times in succession and I can't see why?

**jaywalkar** · 04-11-2013, 07:13 PM

That was what I was afraid of. Seems like such an important feature to be omitted. Pardon my ignorance, but surely there is some alternative in Excel 2011 to the find argument.

I have attached a sample workbook that shows you what I am trying to achieve (and why I have the find argument running three times).

If anyone could assist on an alternative to what I am trying to do here I would really appreciate it.

Dummy Workbook.xlsm

**JosephP** · 04-12-2013, 06:42 AM

you're not actually using the searchformat so I don't really see the problem?

I think you missed my point about the find-you are performing the exact same find operation 3 times which is unnecessary

Sub Find_Order_Details()

Dim c As Range
Dim Rng As Range
Dim rFound As Range
Dim FoundValQuant As String, FoundValPrice As String, FoundValCol As Long
        
    Application.ScreenUpdating = False

    Sheets("Products").Visible = True
    Sheets("Products").Activate
    
    Set Rng = Sheets("Products").Range(Cells(5, 2), Cells(5, 2).End(xlDown))
    
    On Error Resume Next
    'Loop through Cans order sheet
    For Each c In Rng
        Set rFound = Sheets("Cans").Cells.Find(What:=c.Value, _
            LookIn:=xlValues)
        If Not rFound Is Nothing Then
            With rFound
                FoundValQuant = .Offset(-1, 3).Value
                FoundValPrice = .Offset(-1, 1).Value
                FoundValCol = .Offset(-1, 1).Interior.Color
            End With
            c.Offset(0, -1).Value = FoundValQuant
            c.Offset(0, 4).Value = FoundValPrice
            c.Interior.Color = FoundValCol
            c.Offset(0, -1).Interior.Color = FoundValCol
            c.Offset(0, 1).Interior.Color = FoundValCol
            c.Offset(0, 4).Interior.Color = FoundValCol
        End If
    Next c

    Application.ScreenUpdating = True

End Sub