Macro excel

Magpie Bavarde
Magpie Bavarde used Ask the Experts™
on
Hello the experts !

I got this little macro I use on horizontal plannings to go to today cell

Sub GoToToday()
'On déverrouille la feuille, on se positionne
    ActiveSheet.Unprotect
    Cells.Select
'Puis on va chercher la cellule qui contient la date du jour et on la sélectionne
    For Each Cell In ActiveSheet.Range("Y3:PG3")
    If Cell.Value = [Today()] Then
    Cell.Select
    End If
Next
End Sub


Works perfectly fine

But I need something more from this macro today, I would like to check if the "today" cell is part of a grouped range and if it is, ungroup this range (without clearing the outline, just expand and leave it that way)

Your help is very welcome !

Kind regards,

Mélanie
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Team Lead
Commented:
try add:

Columns(cell.Column).ShowDetail = True

Open in new window


to apply:

Sub GoToToday()
    Dim cell As Range
    'On déverrouille la feuille, on se positionne
    ActiveSheet.Unprotect
    Cells.Select
    'Puis on va chercher la cellule qui contient la date du jour et on la sélectionne
    For Each cell In ActiveSheet.Range("Y3:PG3")
        If cell.Value = [Today()] Then
            cell.Select
            Columns(cell.Column).ShowDetail = True
            Exit For
        End If
    Next
End Sub

Open in new window

Magpie BavardeExecutive Assistant

Author

Commented:
Working perfectly fine thank you so much !

I've just inversed first expand then select the cell to arrive directly on the cell rather than "losing" it a bit after the expanse

Thank you very much for your help Ryan, have a great day (you've just made mine ^^)

Kind regards

Mélanie
Magpie BavardeExecutive Assistant

Author

Commented:
Aaaah there is a little issue... If it's already expanded, there's an error message

"Run-time error "1004 : Unable to set the ShowDetail property of the Range class"

Is there a simple way to fix that pretty please ? :)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Ryan ChongSoftware Team Lead

Commented:
you could try this instead:

If Columns(cell.Column).ShowDetail = False Then Columns(cell.Column).ShowDetail = True

Open in new window

Magpie BavardeExecutive Assistant

Author

Commented:
I'm pretty sure it should work and just missing a very simple thing I don't get as I'm such a newbie...
Different error messages, I got a syntax issue I think !

Sub GoToToday()
    Dim cell   As Range
    'On déverrouille la feuille, on se positionne
    ActiveSheet.Unprotect
    Cells.Select
    'Puis on va chercher la cellule qui contient la date du jour et on la sélectionne
    For Each cell In ActiveSheet.Range("Y3:PG3")
        If cell.Value = [Today()] Then
            cell.Select
            If Columns(cell.Column).ShowDetail = False Then
                Columns(cell.Column).ShowDetail = True
                Exit For
            End If
        Next
End Sub
Ryan ChongSoftware Team Lead

Commented:
you have missed out the last "End If".

so try this:

Sub GoToToday()
    Dim cell   As Range
    'On déverrouille la feuille, on se positionne
    ActiveSheet.Unprotect
    Cells.Select
    'Puis on va chercher la cellule qui contient la date du jour et on la sélectionne
    For Each cell In ActiveSheet.Range("Y3:PG3")
        If cell.Value = [Today()] Then
            cell.Select
            If Columns(cell.Column).ShowDetail = False Then
                Columns(cell.Column).ShowDetail = True
                Exit For
            End If
         End If
        Next
End Sub
Magpie BavardeExecutive Assistant

Author

Commented:
Thank you again ! But it doesn't work, it selects the right cell but doesn't expand the columns...

I attach the file, may be I'm missing something obvious ??
2019_TeamPlanning-321-_v33.xlsm
Magpie BavardeExecutive Assistant

Author

Commented:
I will open a new thread as I understand it's best to do a new one for each question, I hope I'm right, if I'm not please let me know :)

Thanks again for your help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial