How can i optimize these lines of "If range ("...") = Empty"

[Slayer_17]

Sub Entrada()
'
' Entrada Macro

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False



'Verifica as celulas se estão vazias
If Range("D4") = Empty Then
MsgBox "Medicamento não fornecido!", vbCritical, "Excluir"
Range("D7").Activate
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Exit Sub
End If

If Range("F7") = Empty Then
MsgBox "Lote não fornecido!", vbCritical, "Excluir"
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Exit Sub
End If

If Range("K7") = Empty Then
MsgBox "Quantidade não fornecida!", vbCritical, "Excluir"
Range("K7").Activate
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Exit Sub
End If

If Range("I7") = Empty Then
MsgBox "Forneça um Lugar!", vbCritical, "Excluir"
Range("I7").Activate
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Exit Sub
End If

...

My code checks if the cells are filled in to register the values correctly. the problem is that it repeats in other lines too, causing the code to be more than 1000 lines long.

Is there any way to optimize this check?

I tried some modifications but without success.

 

[RoryA]

Your code doesn't appear to always react quite the same way for each cell - sometimes it selects the cell being checked, sometimes it doesn't, and sometimes it selects a different cell. Is that accurate?

 

[Slayer_17]

In fact it checks if it is empty and gives an error message pointing to a certain cell.

For example if could do it in this way it would be very good.

...

If Range("D4") or ("e7") or ("k7") or ("i7") = Empty Then
MsgBox "Make sure the cells are filled in correctly!", vbCritical, "Excluir"
Range("D4").Activate
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Exit Sub
End If

...

 

[johnnyL]

Here is one way you could shorten/condense the code:

Sub Entrada()
'
    Dim ArrayRow            As Long
    Dim AddressArray()      As Variant
    Dim MessageArray()      As Variant
'
    Application.ScreenUpdating = False                                                                      ' Turn off ScreenUpdating
    Application.Calculation = xlCalculationManual                                                           ' Disable Automatic Calculation
    Application.EnableEvents = False                                                                        ' Disable Events
'
    AddressArray = Array("D4", "F7", "K7")                                                                  ' Array of addresses to check
    MessageArray = Array("Medicamento não fornecido!", "Lote não fornecido!", "Quantidade não fornecida!")  ' Array of Messages to displahy
'
'Verifica as celulas se estão vazias
    For ArrayRow = LBound(AddressArray) To UBound(AddressArray)                                             ' Loop through AddressArray
        If Range(AddressArray(ArrayRow)) = Empty Then                                                       '   If address is empty then ...
            MsgBox MessageArray(ArrayRow), vbCritical, "Excluir"                                            '       Display message associated with that empty address
'
            If ArrayRow = 1 Then Range("D7").Activate                                                       '       Activate address if ArrayRow = 1
            If ArrayRow = 3 Then Range("K7").Activate                                                       '       Activate address if ArrayRow = 3
'
            Application.EnableEvents = True                                                                 '       Enable Events
            Application.Calculation = xlCalculationAutomatic                                                '       Enable Automatic Calculation
            Application.ScreenUpdating = True                                                               '       Turn on ScreenUpdating
'
            Exit Sub                                                                                        '       Exit this sub if address was found to be empty
        End If
    Next                                                                                                    ' Loop back
End Sub

 

[Slayer_17]

Here is one way you could shorten/condense the code:

Sub Entrada()
'
    Dim ArrayRow            As Long
    Dim AddressArray()      As Variant
    Dim MessageArray()      As Variant
'
    Application.ScreenUpdating = False                                                                      ' Turn off ScreenUpdating
    Application.Calculation = xlCalculationManual                                                           ' Disable Automatic Calculation
    Application.EnableEvents = False                                                                        ' Disable Events
'
    AddressArray = Array("D4", "F7", "K7")                                                                  ' Array of addresses to check
    MessageArray = Array("Medicamento não fornecido!", "Lote não fornecido!", "Quantidade não fornecida!")  ' Array of Messages to displahy
'
'Verifica as celulas se estão vazias
    For ArrayRow = LBound(AddressArray) To UBound(AddressArray)                                             ' Loop through AddressArray
        If Range(AddressArray(ArrayRow)) = Empty Then                                                       '   If address is empty then ...
            MsgBox MessageArray(ArrayRow), vbCritical, "Excluir"                                            '       Display message associated with that empty address
'
            If ArrayRow = 1 Then Range("D7").Activate                                                       '       Activate address if ArrayRow = 1
            If ArrayRow = 3 Then Range("K7").Activate                                                       '       Activate address if ArrayRow = 3
'
            Application.EnableEvents = True                                                                 '       Enable Events
            Application.Calculation = xlCalculationAutomatic                                                '       Enable Automatic Calculation
            Application.ScreenUpdating = True                                                               '       Turn on ScreenUpdating
'
            Exit Sub                                                                                        '       Exit this sub if address was found to be empty
        End If
    Next                                                                                                    ' Loop back
End Sub

It's perfect, thanks a lot for your help. I would never have thought it could be done that way.

 

[johnnyL]

Glad to help.