lundi 20 avril 2015

VBA written in Excel for Windows not working on Mac

I have a set of macros to hide and unhide columns based on the contents of a specific row. They were all written in Excel 2013 for Windows (running in parallels on my MBA, if that's relevant) and work fine there. But when I open the worksheet in Excel 2011 for Mac, the macros give odd results. The "unhide all columns" macro works fine; the other functions get as far as hiding all columns but not as far as unhiding the ones I want to see.

I can only assume Excel for Mac is having a problem with what's in the FOR EACH loop, but I can't figure out what! I'd appreciate any guidance: I need to get this system working on both Windows and Mac.

Code below.

This function works:

Sub GANTT_Filter_Show_All()

Dim rngDates As Range

Set rngDates = Range("GANTT_Dates")

rngDates.EntireColumn.Hidden = False

End Sub

But this one only hides all the columns:

Sub GANTT_Filter_This_Quarter()

Dim intCurrentMonth As Integer, intCurrentYear As Integer, rngDates As Range, cell As Range
Dim intCurrentQuarterMonths(3) As Integer

Set rngDates = Range("GANTT_Dates")
intCurrentMonth = DatePart("m", Date)
intCurrentYear = DatePart("yyyy", Date)

'loading months of current quarter into an array intCurrentMonth

Select Case intCurrentMonth
    Case 1 To 3
        intCurrentQuarterMonths(0) = 1
        intCurrentQuarterMonths(1) = 2
        intCurrentQuarterMonths(2) = 3
    Case 4 To 6
        intCurrentQuarterMonths(0) = 4
        intCurrentQuarterMonths(1) = 5
        intCurrentQuarterMonths(2) = 6
    Case 7 To 9
        intCurrentQuarterMonths(0) = 7
        intCurrentQuarterMonths(1) = 8
        intCurrentQuarterMonths(2) = 9
    Case 10 To 12
        intCurrentQuarterMonths(0) = 10
        intCurrentQuarterMonths(1) = 11
        intCurrentQuarterMonths(2) = 12
    End Select

'hiding all columns

rngDates.EntireColumn.Hidden = True

'comparing each column to array of months in current quarter and hiding if false

For Each cell In rngDates
    For Each v In intCurrentQuarterMonths
        If v = DatePart("m", cell.Value) And DatePart("yyyy", cell.Value) = intCurrentYear Then cell.EntireColumn.Hidden = False
    Next v
Next cell

Application.Goto Reference:=Range("a1"), Scroll:=True

End Sub

Aucun commentaire:

Enregistrer un commentaire