Stopping Macro If either cell is empty

[r4ymond88]

Hello Guys,

I'm VERY new to the whole macro writing thing and using Visual Basics. I'm doing this to impress the bosses and show them I can take on a task.

I have a sheet that basically inserts values into a data table that is later drawn into a pivot table.

I'm not the only person that handles the interface, so I want to make sure that all cells that need to be filled out are actually filled out before the data is sent to the table.

I've tried searching online and when I thought I found a code that worked, it kept giving me the warning message as if the cell was empty even though it was filled out correctly.

This is my code, I need the macro to detect if either cells destino, oferta, demanda or real are empty before it activates worksheet data no show. I also need a warning message for each cell.

Any ideas? Thanks!!!!

Sub guardar()
'seleccionar hoja de UI para copiar los datos
Worksheets("UI").Activate

'copiar los valores del UI a variables
Dim data_values(16) As Variant

data_values(0) = Range("fecha").Value
data_values(1) = Range("routing").Value
data_values(2) = Range("destino").Value
data_values(3) = Range("oferta").Value
data_values(4) = Range("demanda").Value
data_values(5) = Range("real").Value
data_values(6) = Range("entrando").Value
data_values(7) = Range("hold").Value
data_values(8) = Range("holdp").Value
data_values(9) = Range("comat").Value
data_values(10) = Range("NS").Value
data_values(11) = Range("NSP").Value
data_values(12) = Range("week").Value
data_values(13) = Range("dia").Value
data_values(14) = Range("mes").Value
data_values(15) = Range("comentarios").Value

'warning message for empty cells



'seleccionar hoja de datos
Worksheets("data no show").Activate

'seleccionar la celda donde vamos a pegar los valores
Range("table_start").Select
Selection.End(xlDown).Select
'create range for data values
Dim input_range As Range
'set the correct cell to the range
Set input_range = Selection.Offset(1, 0)


'copiar los datos en la hoja correspondiente
For i = 1 To 16
input_range.Offset(0, i - 1).Value = data_values(i - 1)
input_range.Offset(0, 2 - 1).Value = data_values(2 - 1)
Next i

'copiar los valores del Data No Show a variables
'regresar a la hoja inicial
Worksheets("UI").Activate

'limpiar las celdas
Range("destino").ClearContents
Range("oferta").ClearContents
Range("demanda").ClearContents
Range("real").ClearContents
Range("entrando").ClearContents
Range("hold").ClearContents
Range("comat").ClearContents
Range("comentarios").Select
Selection.ClearContents

'regresar a celda
Range("destino").Select

End Sub

 

[patel45]

I think it would be easier using ranges addresses instead of ranges names, paste here a link to a sample file

 

[AlphaFrog]

Hi and welcome to the forum.

Try something like this.

    [color=darkblue]Select[/color] [color=darkblue]Case[/color] [color=darkblue]True[/color]
        [color=darkblue]Case[/color] Len(Range("destino")) = 0
            MsgBox "Missing destino. ", vbExclamation, "Process Canceled"
            [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
        [color=darkblue]Case[/color] Len(Range("oferta")) = 0
            MsgBox "Missing oferta. ", vbExclamation, "Process Canceled"
            [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
        [color=darkblue]Case[/color] Len(Range("demanda")) = 0
            MsgBox "Missing demanda. ", vbExclamation, "Process Canceled"
            [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
        [color=darkblue]Case[/color] Len(Range("real")) = 0
            MsgBox "Missing real. ", vbExclamation, "Process Canceled"
            [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=darkblue]End[/color] [color=darkblue]Select[/color]

 

[r4ymond88]

Hi and welcome to the forum.

Try something like this.

    [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]True[/COLOR]
        [COLOR=darkblue]Case[/COLOR] Len(Range("destino")) = 0
            MsgBox "Missing destino. ", vbExclamation, "Process Canceled"
            [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
        [COLOR=darkblue]Case[/COLOR] Len(Range("oferta")) = 0
            MsgBox "Missing oferta. ", vbExclamation, "Process Canceled"
            [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
        [COLOR=darkblue]Case[/COLOR] Len(Range("demanda")) = 0
            MsgBox "Missing demanda. ", vbExclamation, "Process Canceled"
            [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
        [COLOR=darkblue]Case[/COLOR] Len(Range("real")) = 0
            MsgBox "Missing real. ", vbExclamation, "Process Canceled"
            [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]

This worked perfectly. Thank You!