Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CORRECTION NEEDED
Can please any body know that where and what macro code I can add in
macro below if I want MsgBox to appeare saying that "ZERO BUDGET IN AGRESSO" when there text appeare of "ZERO BUDGET" in any cell of Range ("K25:K62") MACRO:- Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range Const WS_RANGE As String = "B25:D62" On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If Me.Cells(.Row, "B").Value < "" And _ Me.Cells(.Row, "C").Value < "" Then If IsError(Application.Match(Me.Cells(.Row, "O").Value, Columns(27), 0)) Then MsgBox "NO BUDGET IN AGRESSO", vbInformation, "INFORMATION" End If End If End With End If Set MyRange = Range("F25:F62") If Target.Cells.Count = 1 Then If Not Intersect(Target, Range("F25:F62")) Is Nothing Then If IsNumeric(Target) Then budget = WorksheetFunction.VLookup(Target.Offset(0, 9).Value, Range("AB1:AC9995"), 2, False) On Error Resume Next For Each c In MyRange If c.Address < Target.Address Then If c.Offset(0, 9).Value = Target.Offset(0, 9).Value Then budget = budget + c.Value End If End If Next c On Error GoTo ws_exit If Target.Value < "" Then Target.Offset(0, 5).Value = budget Else Target.Offset(0, 5).Value = "" End If End If End If End If ws_exit: Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CORRECTION NEEDED
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Excel.Range Dim rFound As Excel.Range Const WS_RANGE As String = "A1:A10" Const csMSG As String = "ZERO BUDGET IN AGRESSO" Set myRange = Me.Range(WS_RANGE) 'Is Target one of selected cells If Union(myRange, Target).Address = myRange.Address Then If Target.Value = 0 Then 'will also show if cell made empty MsgBox csMSG End If End If End Sub K wrote: Can please any body know that where and what macro code I can add in macro below if I want MsgBox to appeare saying that "ZERO BUDGET IN AGRESSO" when there text appeare of "ZERO BUDGET" in any cell of Range ("K25:K62") MACRO:- Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range Const WS_RANGE As String = "B25:D62" On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If Me.Cells(.Row, "B").Value < "" And _ Me.Cells(.Row, "C").Value < "" Then If IsError(Application.Match(Me.Cells(.Row, "O").Value, Columns(27), 0)) Then MsgBox "NO BUDGET IN AGRESSO", vbInformation, "INFORMATION" End If End If End With End If Set MyRange = Range("F25:F62") If Target.Cells.Count = 1 Then If Not Intersect(Target, Range("F25:F62")) Is Nothing Then If IsNumeric(Target) Then budget = WorksheetFunction.VLookup(Target.Offset(0, 9).Value, Range("AB1:AC9995"), 2, False) On Error Resume Next For Each c In MyRange If c.Address < Target.Address Then If c.Offset(0, 9).Value = Target.Offset(0, 9).Value Then budget = budget + c.Value End If End If Next c On Error GoTo ws_exit If Target.Value < "" Then Target.Offset(0, 5).Value = budget Else Target.Offset(0, 5).Value = "" End If End If End If End If ws_exit: Application.EnableEvents = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
CORRECTION NEEDED
On Jan 22, 11:38*am, dbKemp wrote:
Private Sub Worksheet_Change(ByVal Target As Range) * *Dim myRange As Excel.Range * *Dim rFound As Excel.Range * *Const WS_RANGE As String = "A1:A10" * *Const csMSG As String = "ZERO BUDGET IN AGRESSO" * *Set myRange = Me.Range(WS_RANGE) * *'Is Target one of selected cells * *If Union(myRange, Target).Address = myRange.Address Then * * * * *If Target.Value = 0 Then * * * * * * 'will also show if cell made empty * * * * * * MsgBox csMSG * * * * *End If * *End If End Sub K wrote: Can please any body know that where and what macro code I can add in macro below if I want MsgBox to appeare saying that "ZERO BUDGET IN AGRESSO" when there text appeare of "ZERO BUDGET" in any cell of Range ("K25:K62") MACRO:- Private Sub Worksheet_Change(ByVal Target As Range) * Dim MyRange As Range * Const WS_RANGE As String = "B25:D62" * On Error GoTo ws_exit * Application.EnableEvents = False * If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then * * With Target * * * If Me.Cells(.Row, "B").Value < "" And _ * * * * Me.Cells(.Row, "C").Value < "" Then * * * * If IsError(Application.Match(Me.Cells(.Row, "O").Value, Columns(27), 0)) Then * * * * * MsgBox "NO BUDGET IN AGRESSO", vbInformation, "INFORMATION" * * * * End If * * * End If * * End With * End If * Set MyRange = Range("F25:F62") * If Target.Cells.Count = 1 Then * * If Not Intersect(Target, Range("F25:F62")) Is Nothing Then * * * If IsNumeric(Target) Then * * * * budget = WorksheetFunction.VLookup(Target.Offset(0, 9)..Value, Range("AB1:AC9995"), 2, False) * * * * On Error Resume Next * * * * For Each c In MyRange * * * * * If c.Address < Target.Address Then * * * * * * If c.Offset(0, 9).Value = Target.Offset(0, 9).Value Then * * * * * * * budget = budget + c.Value * * * * * * End If * * * * * End If * * * * Next c * * * * On Error GoTo ws_exit * * * * If Target.Value < "" Then * * * * * Target.Offset(0, 5).Value = budget * * * * Else * * * * * Target.Offset(0, 5).Value = "" * * * * End If * * * End If * * End If * End If ws_exit: * Application.EnableEvents = True End Sub- Hide quoted text - - Show quoted text - thanks for reply dbkemp. can you please tell me that the code you send me how can i mix this in my macro which i mentioned above. and also will it work if i get text of "ZERO BUDGET". as it seems that it will work if i get 0 value in cell |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Value correction | Excel Discussion (Misc queries) | |||
correction needed | Excel Programming | |||
correction | Excel Discussion (Misc queries) | |||
correction of formula please............ | Excel Worksheet Functions | |||
Formula correction HELP!!! | Excel Worksheet Functions |