Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
424 error on a Worksheet Change Macro
Hi. I have a Worksheet Change routine that doesn't do anything when the Target range is changed. When I run the any of the lines of code after the Else statement in the Immediate Window, it produces a 424 error Object required. I'm just not seeing/understanding the problem. Need some help please Here is the code: Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range, res As String Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet Set wks1 = Worksheets("Status Log") Set wks2 = Worksheets("Setup Sheet") Set wks3 = ActiveSheet Set rng = wks1.Range("SVDBStatusLog") 'multiple column database If Target.Address = "$D$1" Then res = Application.VLookup(Target, rng, 1, False) If IsError(res) Then wks3.Range("D2:D6").Value = "Not in Database, " & _ "manual entry required" Else wks3.Range("D2").Value = Application.VLookup(Target, _ rng, 4, False) wks3.Range("D3").Value = Application.VLookup(Target, _ rng, 5, False) wks3.Range("D4").Value = wks2.Range("Job_Name") wks3.Range("D5").Value = Application.VLookup(Target, _ rng, 11, False) wks3.Range("D6").Value = Application.VLookup(Target, _ rng, 11, False) End If End If End Sub -- Casey ------------------------------------------------------------------------ Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545 View this thread: http://www.excelforum.com/showthread...hreadid=572420 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
424 error on a Worksheet Change Macro
Since this code is behind the worksheet, you don't need to declare wks3 or set
it to the Activesheet. You can use the Me keyword--Me is the thing that owns the code--in this case the worksheet. And I'm not sure if this is a typo, but you're returning column 11 twice--for D5 and D6. Did you really want this? And when your code makes changes to the worksheet, it'll fire the _change event. To turn this kind of recursion off, you can disable events (and re-enable them when you're done making changes. And when there is no match in the first column, res won't be a string--it'll be an error value. Like #n/a in a worksheet. So instead of declaring Res as a String, you should declare it as a Variant. This worked ok for me: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim res As Variant 'could be an error Dim wks1 As Worksheet Dim wks2 As Worksheet Set wks1 = Worksheets("Status Log") Set wks2 = Worksheets("Setup Sheet") Set rng = wks1.Range("SVDBStatusLog") 'multiple column database If Target.Address = "$D$1" Then res = Application.VLookup(Target, rng, 1, False) Application.EnableEvents = False If IsError(res) Then Me.Range("D2:D6").Value _ = "Not in Database, manual entry required" Else Me.Range("D2").Value = Application.VLookup(Target, rng, 4, False) Me.Range("D3").Value = Application.VLookup(Target, rng, 5, False) Me.Range("D4").Value = wks2.Range("Job_Name").Value Me.Range("D5").Value = Application.VLookup(Target, rng, 11, False) 'Column 11 again??? Me.Range("D6").Value = Application.VLookup(Target, rng, 11, False) End If Application.EnableEvents = True End If End Sub Casey wrote: Hi. I have a Worksheet Change routine that doesn't do anything when the Target range is changed. When I run the any of the lines of code after the Else statement in the Immediate Window, it produces a 424 error Object required. I'm just not seeing/understanding the problem. Need some help please Here is the code: Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range, res As String Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet Set wks1 = Worksheets("Status Log") Set wks2 = Worksheets("Setup Sheet") Set wks3 = ActiveSheet Set rng = wks1.Range("SVDBStatusLog") 'multiple column database If Target.Address = "$D$1" Then res = Application.VLookup(Target, rng, 1, False) If IsError(res) Then wks3.Range("D2:D6").Value = "Not in Database, " & _ "manual entry required" Else wks3.Range("D2").Value = Application.VLookup(Target, _ rng, 4, False) wks3.Range("D3").Value = Application.VLookup(Target, _ rng, 5, False) wks3.Range("D4").Value = wks2.Range("Job_Name") wks3.Range("D5").Value = Application.VLookup(Target, _ rng, 11, False) wks3.Range("D6").Value = Application.VLookup(Target, _ rng, 11, False) End If End If End Sub -- Casey ------------------------------------------------------------------------ Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545 View this thread: http://www.excelforum.com/showthread...hreadid=572420 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
424 error on a Worksheet Change Macro
Dave, Thanks. Works perfectly. This code is a modification of a post by To Ogilvy and of course he had the res varible declared as a variant. must of changed it in my flayling attempt to get it to work. I reall appreciate the code fix and the explainations. And yea, returning column 11 twice is what I needed, it is an intege tracking number for construction plans and specification manuals Normally, these are issued in matching pairs, but precisely because m project manager have a knack for losing or mis-numbering there bi packages is why I'm using code instead of cell formulas to populat these cells, so manual corrections can be made. Welcome to my world boring huh. Many thanks Dave -- Case ----------------------------------------------------------------------- Casey's Profile: http://www.excelforum.com/member.php...nfo&userid=454 View this thread: http://www.excelforum.com/showthread.php?threadid=57242 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|