Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
No error message but code doesn't work
I have this Sub that I am trying to run but it keeps going back to the
beginning everytime it get to this line: ..Range("I36") = Application.WorksheetFunction.VLookup(c, Worksheets("Goals").Range("C3:K54"), 2, False) no errors, no messages. I can't figure out what the problem is. Any help will be appreciated. Thanks. Private Sub Worksheet_Change(ByVal Target As Range) Worksheets("CENTRAL PA Charts").Activate With ActiveSheet For Each c In Worksheets("Goals").Range("C3:C54").Cells If c.Value = .Range("B4") And c.Offset(0, -1).Value = .Range("B3") Then .Range("I36") = Application.WorksheetFunction.VLookup(c, Worksheets("Goals").Range("C3:K54"), 2, False) .Range("I37") = Application.WorksheetFunction.VLookup(c, Worksheets("Goals").Range("rngGoals"), 4, False) Exit For End If Next c End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
No error message but code doesn't work
Temporarily disable events
Application.EnableEvents = false 'your code Application.EnableEvents = true One very minor point. You are using activesheet. In this case not a problem but if somehow you were to change the selected sheet the code could operate on the wrong sheet. Since this code is embeded directly in a sheet you are better off to use Me insetad of activesheet. Me will always refer to the sheet that the code is in. -- HTH... Jim Thomlinson "Ayo" wrote: I have this Sub that I am trying to run but it keeps going back to the beginning everytime it get to this line: .Range("I36") = Application.WorksheetFunction.VLookup(c, Worksheets("Goals").Range("C3:K54"), 2, False) no errors, no messages. I can't figure out what the problem is. Any help will be appreciated. Thanks. Private Sub Worksheet_Change(ByVal Target As Range) Worksheets("CENTRAL PA Charts").Activate With ActiveSheet For Each c In Worksheets("Goals").Range("C3:C54").Cells If c.Value = .Range("B4") And c.Offset(0, -1).Value = .Range("B3") Then .Range("I36") = Application.WorksheetFunction.VLookup(c, Worksheets("Goals").Range("C3:K54"), 2, False) .Range("I37") = Application.WorksheetFunction.VLookup(c, Worksheets("Goals").Range("rngGoals"), 4, False) Exit For End If Next c End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
chart error message / work sheet not open ? | Charts and Charting in Excel | |||
Code has been interupted - error message | Excel Discussion (Misc queries) | |||
Help with Error Message Box code | Excel Programming | |||
Message box causing error in code | Excel Programming | |||
Message box causing error in code | Excel Programming |