jeudi 28 mars 2019

multiple targets in multiple ifs, depending on one another VBA

I have a data table, in which, set of rows need to be hidden based on criteria set in different set of cells. I wrote something which basically is "cave man coding" and of course it does not work :)

I have created if's and tried to put ifs within ifs, but literally nothing is happening with this code.

I only wrote it 2 sets of rows, but it is around 30 different sets (not yet written)

Sub Worksheet_Change(ByVal Target As Range)
Set Target = Range(Cells(7, 8), Cells(7, 8))
Set Target1 = Range(Cells(3, 2), Cells(3, 2))
    If Target.Value = "No"
        Rows("8:29").EntireRow.Hidden = True
        If Target.Value = "Yes" And Target1.Value = "Half" Then
            Rows("22:29").EntireRow.Hidden = True
        ElseIf Target.Value = "Yes" And Target1.Value = "Full" Then
            Rows("8:29").EntireRow.Hidden = False
        End If
    End If

Set Target = Range(Cells(30, 8), Cells(30, 8))
Set Target1 = Range(Cells(3, 2), Cells(3, 2))
    If Target.Value = "No"
        Rows("31:56").EntireRow.Hidden = True
        If Target.Value = "Yes" And Target1.Value = "Half" Then
            Rows("47:56").EntireRow.Hidden = True
        ElseIf Target.Value = "Yes" And Target1.Value = "Full" Then
            Rows("31:56").EntireRow.Hidden = False
        End If
    End If
End Sub

So, in a short summary i have two variables in cell B3 (Half and Full)

and in Row H in specified cells (e.g. H7, H30 and so on) Yes/No options. rows 7, 30 (any row, having Yes/no option) are headers of topics from 8 to 29 details of this topic are included.

if H7 (H30 ...) is No - entire details should be hidden (rows 8-29; 31-56 and so on) - Valua in B3 does not matter. If H7 (H30...) is Yes, then value in B3 matters: If H7 (H30...) is Yes and B3 is half - rows 22-29 hidden (47-56 and so on) rows 8-21 are unhidden in this case If H7 (H30...) is Yes and B3 is full - rows 8-29 are unhidden.

Hope i explained it well.

Please help me to improve my code, to be able to do set goal.

Aucun commentaire:

Enregistrer un commentaire