Vba data validation

QuestionEdit

QUESTION: I have one question regarding my macro. I have written below mentioned macro and my expectation is as follow:

If I insert (it will be done by macro processing) value ("X" - represent info about final delivery of something) into col AG, then I have to check col AR (info about payment date of something). If is a date value in col AR < 2011 then I have to check col E (Sum). If is col E10 <> "" then to show msgbox. If is "", then exit macro. In case the date value in col AR is > 2010 then to check col S. If is value in col S <> "" then to show msgbox, if is "", then exit macro. Macro is used to range AR10:AR50.

Problem is, that msgbox is displaying several time after the other macro inserted value "X" into col AG, not only one msgbox refer to one case.

And I don't know what is wrong. Can you help me please?

I would appriciate your help. Martin

My macro is: Sub FinalDeliveryCapex()

Dim ECapex As Range, LOpex, SCapex, ColAG, ColAR Dim DateCap As Date, DateOp Dim Cell As Range Const FinDelCap As String = "X"

' CAPEX posledni dodavka For Each Cell In Range("AG10:AG50")

Set ECapex = ThisWorkbook.Worksheets("Budget").Cells(Cell.Row, "E") '2010 Set SCapex = ThisWorkbook.Worksheets("Budget").Cells(Cell.Row, "S") '2011 Set ColAG = ThisWorkbook.Worksheets("Budget").Cells(Cell.Row, "AG") DateCap = ThisWorkbook.Worksheets("Budget").Cells(Cell.Row, "AR")

If UCase(Trim(ColAG)) = "X" Then

 If Year(DateCap) < 2011 Then
   If ECapex <> "" Then
      Cell.EntireRow.Select
      MsgBox ("Zkontrolujte buňku E" & Cell.Row & ", polo?ka NK byla označena za fin¨¢ln¨ª a je nenulov¨¢ č¨¢stka na Obligu")
   End If
 End If
 If Year(DateCap) > 2010 Then
   If SCapex <> "" Then
     Cell.EntireRow.Select
     MsgBox ("Zkontrolujte buňku S" & Cell.Row & ", polo?ka NK byla označena za fin¨¢ln¨ª a je nenulov¨¢ č¨¢stka na Obligu")
   End If
 End If

End If

Next Cell

End Sub

ANSWER: You can put Exit Sub after each of the 2 MsgBox statements, so you'll only get one. Currently, it's in a loop

---------- FOLLOW-UP ----------

QUESTION: I am not sure if I understand right. Should I devide the macro as follows?

My updated macro is: Sub FinalDeliveryCapex()

Dim ECapex As Range, LOpex, SCapex, ColAG, ColAR Dim DateCap As Date, DateOp Dim Cell As Range Const FinDelCap As String = "X"

' CAPEX posledni dodavka For Each Cell In Range("AG10:AG50")

Set ECapex = ThisWorkbook.Worksheets("Budget").Cells(Cell.Row, "E") '2010 Set ColAG = ThisWorkbook.Worksheets("Budget").Cells(Cell.Row, "AG") DateCap = ThisWorkbook.Worksheets("Budget").Cells(Cell.Row, "AR")

If UCase(Trim(ColAG)) = "X" Then

If Year(DateCap) < 2011 Then
  If ECapex <> "" Then
     Cell.EntireRow.Select
     MsgBox ("Zkontrolujte buňku E" & Cell.Row & ", polo?ka NK byla označena za fin¨¢ln¨ª a je nenulov¨¢ č¨¢stka na Obligu")
  End If
End If

Next Cell

End Sub

and should I do the same for year 2011?

Martin

AnswerEdit

NO: Sub FinalDeliveryCapex()

Dim ECapex As Range, LOpex, SCapex, ColAG, ColAR Dim DateCap As Date, DateOp Dim Cell As Range Const FinDelCap As String = "X"

' CAPEX posledni dodavka For Each Cell In Range("AG10:AG50")

Set ECapex = ThisWorkbook.Worksheets("Budget").Cells(Cell.Row, "E") '2010 Set SCapex = ThisWorkbook.Worksheets("Budget").Cells(Cell.Row, "S") '2011 Set ColAG = ThisWorkbook.Worksheets("Budget").Cells(Cell.Row, "AG") DateCap = ThisWorkbook.Worksheets("Budget").Cells(Cell.Row, "AR")

If UCase(Trim(ColAG)) = "X" Then

If Year(DateCap) < 2011 Then
  If ECapex <> "" Then
     Cell.EntireRow.Select
     MsgBox ("Zkontrolujte buňku E" & Cell.Row & ", polo?ka NK byla označena za fin¨¢ln¨ª a je nenulov¨¢ č¨¢stka na Obligu")
      Exit Sub   '<===========added here
  End If
End If
If Year(DateCap) > 2010 Then
  If SCapex <> "" Then
    Cell.EntireRow.Select
    MsgBox ("Zkontrolujte buňku S" & Cell.Row & ", polo?ka NK byla označena za fin¨¢ln¨ª a je nenulov¨¢ č¨¢stka na Obligu")
      Exit Sub   '<===========added here
  End If
End If

End If

Next Cell

End Sub