Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO IS NOT COMPLETE
Hi , I want to add line in macro (please see below) that if I have
text of "ZERO BUDGET" appear in any cell of Range("K25:K62") then I should get Message box appear that there is Zero budget. What should i put in macro below that do the work which i mentioned above 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
|
|||
|
|||
MACRO IS NOT COMPLETE
For Each cell In Range("K25:K62")
If cell.Text = "ZERO BUDGET" Then MsgBox "Zero Budget", vbInformation Exit For End If Next cell -- Dan On Jan 4, 3:16*pm, K wrote: Hi , I want to add line in macro (please see below) that if I have text of "ZERO BUDGET" appear in any cell of Range("K25:K62") then I should get Message box appear that there is Zero budget. What should i put in macro below that do the work which i mentioned above 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
|
|||
|
|||
MACRO IS NOT COMPLETE
Here's another way.
If WorksheetFunction.CountIF(Range("K25:K62"),"ZERO BUDGET") 0 Then MsgBox "I found Zero Budget in this range." End if HTH, JP "K" wrote in message ... Hi , I want to add line in macro (please see below) that if I have text of "ZERO BUDGET" appear in any cell of Range("K25:K62") then I should get Message box appear that there is Zero budget. What should i put in macro below that do the work which i mentioned above 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro wait 30 seconds then Complete the Macro | Excel Discussion (Misc queries) | |||
Inefficient macro won't complete | Excel Programming | |||
Please I need some help to complete a VBA Macro | Excel Discussion (Misc queries) | |||
Complete Newbe - Is this a MACRO function or VB ? | Excel Programming | |||
macro to complete missing data | Excel Programming |