Automatic Macros Invocation

  • Thread starter Thread starter pthien
  • Start date Start date
P

pthien

The code for my sub is as listed below:-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyRange As Range
Set MyRange = Range(Cells(1, "a"), Cells(2, "b"))
If Target.Address = Range("MyRange").Address Then
Cells(1, "c") = Cells(1, "a") * Cells(1, "b")
Cells(2, "c") = Cells(2, "a") + Cells(2, "b")
End If
End Sub


I am trying to automatically invoke the macros when the values in the cells
for MyRange are changed.
..

I am getting a runtime error '1004'


Can anyone help me, please
 
pthien

Range("MyRange") is not valid. It should be

If Target.Address = MyRange.Address Then

Also, the SelectionChange event fires when the a cell is selected, not when
it's changed. You may want the Worksheet_Change event.
 
Hi,
The cells function takes numeric arguments not letters.

Set MyRange = Range(Cells(1, 1), Cells(2, 2))
If Target.Address = Range("MyRange").Address Then
Cells(1, 3) = Cells(1, 1) * Cells(1, 2)
Cells(2, 3) = Cells(2, 1) + Cells(2, 2)
 
John

That's not true. The ColumnIndex argument is a Variant that takes numbers
and letters. Check out help for the Item property of the Range object.
 
Hi Dick,
I was thinking strings not variables, but I see I would have been wrong
about that too.
 
Just to add

'Dim MyRange as Range
Range(Cells(1, "a"), Cells(2, "b")).Name = "MyRange"
If Target.Address = Range("MyRange").Address Then

is an example of where quotes would be appropriate.

Dim MyRange as String
MyRange = "A1:B2"

If Target.Address = Range(MyRange).Address then

would be a case where quotes would not be appropriate (as you state)

Regards,
Tom Ogilvy
 
Back
Top