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
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
Advertisement