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.
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.