PDA

View Full Version : Solved: Target.Offset(0,2) & Run-time error



starsky
09-08-2010, 07:40 AM
Hi,

Below is a worksheet change event routine I'm compiling. The aim of the code is to do a look up and place the result a couple of columns to the right of the target cell.

In this instance, the routine is fine since it uses target.Offset(0,1). It correctly places the result in the cell next to the target cell. However, if I use target.Offset(0,2) I get a run-time error 1004. I don't understand why this should be.

Private Sub worksheet_change(ByVal target As Range)
Dim res As Variant
Dim myTable As Range
Dim rg1 As Range

With Worksheets("look")
Set myTable = .Range("looker")
End With

Set rg1 = target.Offset(0, 1)
If target.Cells.Count > 1 Then Exit Sub
If target.Row > 1 And target.Column = 2 Then
res = Application.VLookup(target.Value, myTable, 2, False)
End If

If IsError(res) = False Then
rg1.Value = res
End If

End Sub


Any thoughts?

Many thanks.

p45cal
09-08-2010, 10:21 AM
Yes, it's calling itself umpteen times, blanking any values in cells to the right whether the offset is 1 or 2. You need to stop it calling itself either with
Application.enableevents=false/truePrivate Sub worksheet_change(ByVal target As Range)
Dim res As Variant
Dim myTable As Range
Dim rg1 As Range

Set myTable = Worksheets("look").Range("looker")
Set rg1 = target.Offset(0, 1)
If target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If target.Row > 1 And target.Column = 2 Then res = Application.VLookup(target.Value, myTable, 2, False)
If IsError(res) = False Then rg1.Value = res
Application.EnableEvents = True
End Sub
or by asking if it's in column B much earlier in the macro,Private Sub worksheet_change(ByVal target As Range)
If target.Row > 1 And target.Column = 2 And target.Cells.Count = 1 Then
Dim res As Variant
Dim myTable As Range
Dim rg1 As Range

With Worksheets("look")
Set myTable = .Range("looker")
End With
Set rg1 = target.Offset(0, 2)
res = Application.VLookup(target.Value, myTable, 2, False)
If IsError(res) = False Then
rg1.Value = res
End If
End If
End Sub
or by using a Static variable (untested):Private Sub worksheet_change(ByVal target As Range)
Static Blocked As Boolean
If target.Cells.Count > 1 Or Blocked Then Exit Sub
Blocked = True
Dim res As Variant
Dim myTable As Range
Dim rg1 As Range

Set myTable = Worksheets("look").Range("looker")
Set rg1 = target.Offset(0, 1)
If target.Row > 1 And target.Column = 2 Then
res = Application.VLookup(target.Value, myTable, 2, False)
If IsError(res) = False Then rg1.Value = res
End If
Blocked = False
End Sub.

starsky
09-09-2010, 01:38 AM
Excellent, thanks. I've gone for option 2 initially, but will check out the others for education purposes.

THanks for your help.