Home>

conditional branch does not work for vba

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.

vba excel
  • 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"}, {"●", "▲", "△"} ), "×")

Related articles