Mac issue (please help) "Compile error: Expected Function or variable"

[m1notaur]

Hello,

I created the code (below) on a PC and the file works great! Our company has started issuing macs to some users and one of the people who got one just ran one of my workbooks and got this error:

"Compile error:
Expected Function or variable"

The error occurs at the first ".ClearContents". Since it is getting hung up there, I can only assume it might get hung up on the others.

ANY HELP WOULD BE APPRECIATED!

Here is the code:

Option Explicit


Private Sub Worksheet_Activate()
    'This code unprotects the sheet using the correct password
ActiveSheet.Unprotect Password:=Worksheets("Backend Data").Range("BC2").Value
    'write "Generating Budget... please be patient." at the bottom of the excel screen while code runs.
Application.StatusBar = "Generating Budget... please be patient."
    '???Could this be code that hides ZERO values???
ActiveWindow.DisplayZeros = True
    'While code is running, don't refresh the screen
Application.ScreenUpdating = False

    'Defining PvtTable as a Pivot Table
Dim PvtTable As PivotTable
    'Defining variable as a range. preceeding variable with "rng" as a mental reminder when looking at it below that it is a range object
Dim rngCellBeingChecked As Range

    'For each Pivot Table in the specified worksheet,
For Each PvtTable In Worksheets("#3 Pivot Table for Budget").PivotTables
    'Refresh the table
    Worksheets("#3 Pivot Table for Budget").Unprotect Password:=Worksheets("Backend Data").Range("BC2").Value
    PvtTable.RefreshTable
    Worksheets("#3 Pivot Table for Budget").Protect Password:=Worksheets("Backend Data").Range("BC2").Value
Next PvtTable

    '**********HIDE/SHOW ROWS************
    'Repeat this process for each rngCellBeingChecked in the following range
    'PRODUCTION, FOOD&ENT, BRANDING, COMMS, and REVENUE - defined by the ranges
For Each rngCellBeingChecked In Range("F5:F59,F62:F116,F119:F153,F156:F210,F213:F224")
   'If the value it is checking = "1", then
    If rngCellBeingChecked.Value = "1" Then
      'make the row visible
       rngCellBeingChecked.EntireRow.Hidden = False
    Else
        'else make the row invisible and delete the dollar value for that line item (so it isn't factored into budget if removed from the budget
        rngCellBeingChecked.EntireRow.Hidden = True And rngCellBeingChecked.Offset(0, -1).ClearContents = True And rngCellBeingChecked.Offset(0, 1).ClearContents = True And rngCellBeingChecked.Offset(0, 2).ClearContents = True And rngCellBeingChecked.Offset(0, 3).ClearContents = True And rngCellBeingChecked.Offset(0, 4).ClearContents = True
    End If
Next
    
    'SUMMARY - doing this separate because I do not want any cell values cleared like the code above is doing
For Each rngCellBeingChecked In Range("F237:F283")
   'If the value it is checking = "1", then
    If rngCellBeingChecked.Value = "1" Then
      'make the row visible
       rngCellBeingChecked.EntireRow.Hidden = False
    Else
        'else make the row invisible
        rngCellBeingChecked.EntireRow.Hidden = True
    End If
Next

    'Refreshes the page after all code runs
Application.ScreenUpdating = True

    'After all the code is run, the next line locks the sheet again
ActiveSheet.Protect Password:=Worksheets("Backend Data").Range("BC2").Value ', DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowSorting:=True, AllowFiltering:=True

    'Remove the Status Bar once the code has finished
Application.StatusBar = False

End Sub

[shg]

I don't know what you're trying to do, but ClearContents is a method that clears a range.

[m1notaur]

Thanks, SHG - I understand that ClearContents is a method that clears a range. It is working great on a PC. I'm only having an issue on Macs with it using the code above.

[shg]

It may work on a PC, but the syntax makes little sense. If you explain what you're trying to do, I'll try to help.

[itsmeiniowa]

Using Mac Excel 2011 - I have a list of over 7000 names. I want to group each name and get a total of the number of times each name occurs in the list. HELP!

[shg]

I meant, more narrowly, what is that line of code supposed to do. Try replacing it with this:

    With rngCellBeingChecked
        .EntireRow.Hidden = True
        .Offset(0, -1).Range("A1, A3:A6").ClearContents
    End With

[m1notaur]

Using Mac Excel 2011 - I have a list of over 7000 names. I want to group each name and get a total of the number of times each name occurs in the list. HELP!

itsmeiniowa - Sorry, man... this is my string... if you got a problem, please create a new post so people can help out with your issue. Good luck.

[m1notaur]

SHG:

I've uploaded an empty version of the spreadsheet... check it out. I didn't know any VBA before doing it and am excited with what I did first time through.

The spreadsheet I have is an elaborate budget tracker for multi-million dollar events we run. On one tab, the user selects which items they would like in their budget (barricades, jumbotrons, lighting, audio/video equipment, judges fees, etc. Once they select these items, they navigate to the second tab which is the active budget. Depending on what they selected on the first tab, these items will appear. This is beneficial to the people I work with because if they don't need item(s) in a budget, this spreadsheet hides them.

To sum up what I am trying to achieve with the code at this point, here it is in laymen's terms:

CHECK A HIDDEN CELL IN EACH ROW (OF A RANGE) TO SEE IF IT HAS A VALUE. IF IT HAS A VALUE ("1"), THEN IT WAS SELECTED ON THE FIRST TAB.
For Each rngCellBeingChecked In Range("F5:F59,F62:F116,F119:F153,F156:F210,F213:F224")

IN A HIDDEN CELL (NOT VISIBLE TO THE USER) ON EACH ROW, IF THE VALUE OF THAT CELL EQUALS "1", THEN IT WAS SELECTED ON THE FIRST TAB SO MAKE THE ROW VISIBLE ELSE
If rngCellBeingChecked.Value = "1" Then
rngCellBeingChecked.EntireRow.Hidden = False
Else

ELSE, THE ITEM IS NOT IN THE BUDGET SO HIDE THE INFO AND DELETE ANY VALUES IN CERTAIN CELLS.
rngCellBeingChecked.EntireRow.Hidden = True And rngCellBeingChecked.Offset(0, -1).ClearContents = True And rngCellBeingChecked.Offset(0, 1).ClearContents = True And rngCellBeingChecked.Offset(0, 2).ClearContents = True And rngCellBeingChecked.Offset(0, 3).ClearContents = True And rngCellBeingChecked.Offset(0, 4).ClearContents = True

I will try what you recommended and test it on a Mac. I'll let you know.