mercredi 27 février 2019

Optimize a chain of IFs that use the .Find function

I've the forum a couple of times to check for answers, but this is the first time I ask a question.

I wrote a code that needs to find a couple of strings, and the actions are based on which strings it has found first. The problem is: right now I have a bunch of "ifs" and I think my code could be smaller. I think I'm writing the same thing twice, but I can't see how to write only once Also, I created some functions to make the code smaller.

I thought about using a Loop until I find one of the words, but I can't find a good way to exit the loop and tell which the word I found. Or use Select Case.

Thank you for your help and I apologize if I haven't followed some rule here.

Below is the function that I use in the code:

Function FindCell(FoundCell As Range, Text As String, RowUnid1 As Integer, RowUnid2 As Integer)

Set wb = ActiveWorkbook

With wb.Sheets("Preparation")

    Set FoundCell = .Range(("A" & RowUnid1), ("A" & RowUnid2)).Find(What:=Text, LookIn:=xlValues, SearchOrder:=xlByColumns)

End With
End Function

This is part of the code, with comments explaining

Call FindCell(FoundCell, "Próxima Intervenção:", RowUnid1, RowUnid2)    

If FoundCell Is Nothing Then                                                    'If it doesn't find "Próxima Intervenção:"
    Call FindCell(FoundCell, "Próximas Interven", RowUnid1, RowUnid2)           'Tries to find "Próximas Interven"

    If FoundCell Is Nothing Then                                                'If it does not find "Próximas Interven"
        Call FindCell(FoundCell, "Previsão de uti", RowUnid1, RowUnid2)         'Tries to find "Previsão de uti"

        If FoundCell Is Nothing Then                                            'If it does not find "Previsão de uti"
            Call FindCell(FoundCell, "Previsão para", RowUnid1, RowUnid2)       'Tries to find "Previsão para"

            If FoundCell Is Nothing Then                                        'If it does not find "Previsão para"
                Call FindRow(RowContato, "Contato", RowUnid1, RowUnid2)         'Finds "Contato", which is a string it will always find
                Call BreakLineTest(RowObjetivo, RowContato, "F", i)             'This is a function that determines wether it's going to copy 1, 2 or 3 lines, starting at the row where it found the word "objetivo" (RowObjetivo) (This part is not in here in the pasted code), using the row "contato" as a parameter

            Else                                                                'If it finds "Previsão para"
                RowPrevisao = FoundCell.Row         'assigns the number of the row where it found "Previsão para"
                Call BreakLineTest(RowObjetivo, RowPrevisao, "F", i)            'Now it uses RowPrevisao as a parameter
            End If

        Else                                                                    'If it finds "Previsão de"
            RowPrevisao = FoundCell.Row
            Call BreakLineTest(RowObjetivo, RowPrevisao, "F", i)                'Now it uses RowPrevisao as a parameter
        End If

    Else                                                                        'If it finds "Próximas Interven"
        RowProxInt = FoundCell.Row
        Call BreakLineTest(RowObjetivo, RowProxInt, "F", i)                     'Uses RowProxInt as a parameter
        Call FindCell(FoundCell, "Previsão de uti", RowUnid1, RowUnid2)         'Finds "Previsao de uti"

        If FoundCell Is Nothing Then                                            'If it does not find Previsão de"
            Call FindCell(FoundCell, "Previsão para", RowUnid1, RowUnid2)       'finds "Previsão para"

            If FoundCell Is Nothing Then                                        'If it doesn't find "Previsão para"
                Call FindRow(RowContato, "Contato", RowUnid1, RowUnid2)         'Finds "contato"
                Call BreakLineTest(RowProxInt, RowContato, "G", i)              'Uses RowContato as a parameter

            Else                                                                'If it finds "Previsão para"
                RowPrevisao = FoundCell.Row
                Call BreakLineTest(RowProxInt, RowPrevisao, "G", i)              'Uses RowProxInt as a parameter

            End If
        Else                                                                    'If it finds "Previsão de"
            RowPrevisao = FoundCell.Row
            Call BreakLineTest(RowProxInt, RowPrevisao, "G", i)                  'Uses ProxInt as parameter

        End If
    End If

Else                                                                            'If it finds "Próxima Intervenção"
    RowProxInt = FoundCell.Row
    Call BreakLineTest(RowObjetivo, RowProxInt, "F", i)                         'Uses ProxInt as parameter
    Call FindCell(FoundCell, "Previsão de uti", RowUnid1, RowUnid2)             'Tries to find "Previsão de"

    If FoundCell Is Nothing Then                                                'If it doesn't find "Previsão de"
        Call FindCell(FoundCell, "Previsão para uti", RowUnid1, RowUnid2)         'Tries to find "Previsão para"

        If FoundCell Is Nothing Then                                            'If it doesn't find "Previsão para"
            Call FindRow(RowContato, "Contato", RowUnid1, RowUnid2)             'Finds "contato"
            Call BreakLineTest(RowProxInt, RowContato, "G", i)
        Else                                                                    'If it finds "Previsão para"
            RowPrevisao = FoundCell.Row
            Call BreakLineTest(RowProxInt, RowPrevisao, "G", i)
        End If

    Else                                                                        'If it finds "Previsão de"
        RowPrevisao = FoundCell.Row
        Call BreakLineTest(RowProxInt, RowPrevisao, "G", i)
    End If
End If

Aucun commentaire:

Enregistrer un commentaire