Home>

Create a management table in the Excel calendar, and work on the moon
I manage.
Every month, we manually enter work days etc., but automation
I received a request to form a VBA because I wanted to do it, but I'd like to know because there are many things I do not understand as a beginner.
* Until now, there was only one condition I wanted to do
I created it with an IF statement.
I made it as follows!
= IF ($L16 = S $13, IF ($A16 = "Complete", "●", "◎"), "×")

.
1.Enter the work day (16 in L column)
Marks the target day of the calendar next to it (from column N).

2, Mark, but it is divided into the following patterns.
* Make sure that the mark matches the conditions.

Condition 1
If the production (16 in G column) is not completed (16 in status A column), ◎

Condition 2

when the production (column G) is completed (status)

Condition 3

if the test (column G) is below completion (status)

Condition 4
If the test (column G) is complete (status), ▲

If none of the conditions apply,

* There are several statuses other than completion.
Example: Completion, request, periodic, emergency etc.
I want to mark with ◎ and △ except for completion!

The Excel file used is as follows.

  • Answer # 1

    Register the following macro (function) in the standard module.

    Public Function SetStatus (ByVal rng As Range)
        Dim day As Variant
        Dim adr As Variant
        Dim wcol As String
        Dim wrow As String
        Application.Volatile
        adr = Split (rng.Address, "$")
        wcol = adr (1)
        wrow = adr (2)
        If Range ("L"&wrow) .Value = Range (wcol&13) .Value Then
            SetStatus = "×"
            If Range ("G"&wrow) .Value = "Production" Then
                If Range ("A"&wrow) .Value = "Done" Then
                    SetStatus = "◎"
                Else
                    SetStatus = "●"
                End If
            End If
            If Range ("G"&wrow) .Value = "test" Then
                If Range ("A"&wrow) .Value = "Done" Then
                    SetStatus = "▲"
                Else
                    SetStatus = "△"
                End If
            End If
        Else
            SetStatus = ""
        End If
    End Function

    And N16 cell
    = SetStatus (N16)
    And copy to the right up to 31 days with AutoFill.
    Copy down as many lines as you need.
    This completes.
    The status is set automatically when the date is entered after L16.

  • Answer # 2

    I don't know the conditions for a while, but if there are more branches, it seems better to useSWITCH

    VLOOKUP+IFERRORseems to be good if there is a status list and you can add some data to the sheet

  • Answer # 3

    I did not understand well, but if you prepare answers for multiple options, you can use the LOOKUP function.

    If G16 prepares an answer for ("production completed", "test completed", "testing"), write as follows.

    = IFERROR (LOOKUP (G16, {"Production complete", "Test complete", "Testing"}, {"●", "▲", "△"} ), "×")