Worksheet change

  • Thread starter Thread starter Andy Brown
  • Start date Start date
A

Andy Brown

This is fairly straightforward -

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Then
Sheets("Sheet1").Select
Range("D1").Select
End If
End Sub

but for the range select I get Runtime Error 1004 -- Select Method of Range
Class Failed. I don't necessarily even want to go to Sheet1, but I get the
same problem with eg:
If Target.Column = 1 Then
Range("Sheet1!D1").Value = Range("B2").Value

I've seen replies to other questions on Worksheet_Change where switching to
other sheets is OK, so I'm assuming it's something to do with my
installation. Any suggestions gratefully received.

TIA,
Andy
 
try
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.Goto reference:=[Sheet1!d1]
End Sub
 
It is just 3 times slower - but does avoid the reference problem.

Regards,
Tom Ogilvy

Don Guillett said:
I didn't suggest you use application with this. Only if using the goto
reference.
[Sheet1!D65536].End(xlUp).Offset(1, 0).Value = [B2]
should work as well.

Andy Brown said:
Gents, I simplified the question in the interests of focussing on the main
problem. I was actually after shunting Sheet2!B1 to the first free cell in
Sheet1!D:D (with D1 as a label). Therefore,

(Don),
Application.[Sheet1!D65536].End(xlUp).Offset(1, 0).Value = Range("B2").Value
worked fine ;

(Mr Ogilvy),
Sheets("Sheet1").Range("D65536").End(xlUp).Offset(1, 0).Value =
Range("B2").Value
worked fine, and the error explanation was excellent.

Thanks much to you both,
Andy
Click to expand...
Click to expand...
 
Tom,
I have a fast computer.<G>

Tom Ogilvy said:
It is just 3 times slower - but does avoid the reference problem.

Regards,
Tom Ogilvy

Don Guillett said:
I didn't suggest you use application with this. Only if using the goto
reference.
[Sheet1!D65536].End(xlUp).Offset(1, 0).Value = [B2]
should work as well.

Andy Brown said:
Gents, I simplified the question in the interests of focussing on the main
problem. I was actually after shunting Sheet2!B1 to the first free
Click to expand...
Click to expand...
cell
in
Sheet1!D:D (with D1 as a label). Therefore,

(Don),
Application.[Sheet1!D65536].End(xlUp).Offset(1, 0).Value = Range("B2").Value
worked fine ;

(Mr Ogilvy),
Sheets("Sheet1").Range("D65536").End(xlUp).Offset(1, 0).Value =
Range("B2").Value
worked fine, and the error explanation was excellent.

Thanks much to you both,
Andy
Click to expand...
Click to expand...
Click to expand...
 
Back
Top