Worksheet_Change help

[Barney Fife]

I can't get my worksheet_change to work! (I'm using Excel XP version).
Any help would be greatly appreciated. I'll try to clearly explain
what I'm doing.

(1)I use the data/web query function to pull pricing information in
every 15 min. on Sheet1. The query dumps results in a range from D1:F7

(2)When cell E3 changes, I want to trigger a macro to take the value
and put it in column A. When the value changes again, take the new
value and put it in the next row in column A. I'm using the following
code:

Private Sub Worksheet_Change (ByVal Target As Excel.Range)
If Target = [E3] Then
new_price=cells(3,5)
lastrow=range("A65000").end(xlup).row+1
cells(lastrow,1)=new_price
End If
End Sub

I get a "type mismatch" error. I've tried setting another cell, say G3
equal to the value of E3 and then changing the target to G3 and
triggering off that but no luck. I can break the link to E3 and change
G3 by hand and it works fine.

[Tomas Kraus]

I tried this:
If Target.Address = "$E$3" Then
new_price = Cells(3, 5)
lastrow = Range("A65000").End(xlUp).Row + 1
Cells(lastrow, 1) = new_price
End If
and it worked exactly as described in your post.

Tomas

"Barney Fife" <bsm...@aol.com> píse v diskusním príspevku
news:k7n3ev4h3p6g7vt2l...@4ax.com...

[Barney Fife]

On Sat, 7 Jun 2003 15:28:26 +0200, "Tomas Kraus" <xkr...@seznam.cz>
wrote:

>I tried this:
>If Target.Address = "$E$3" Then
> new_price = Cells(3, 5)
> lastrow = Range("A65000").End(xlUp).Row + 1
> Cells(lastrow, 1) = new_price
>End If
>and it worked exactly as described in your post.
>
>Tomas

Thanks for the tip but it still doesn't work for me. I can now at
least trigger the event - when I break at "Target.Address" it gives me
a value of "$D$1:$F$7" and then skips the if statment.

[Bob Phillips]

Barney,

When you test a range, irt will pick up the first cell in that range. So if
you select D1:F7, the test

If Target = [E3] Then

is effectively saying If D1 = [E3], which fails.

You need to test for an intersect, like so

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target = [E3]) Is Nothing Then

new_price = Cells(3, 5)
lastrow = Range("A65000").End(xlUp).Row + 1
Cells(lastrow, 1) = new_price
End If

End Sub

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks

"Barney Fife" <bsm...@aol.com> wrote in message
news:o9q3evcfq9fdofl78...@4ax.com...

[Bob Phillips]

Barney,

small error

If Not Intersect(Target = [E3]) Is Nothing Then

should read
If Not Intersect(Target, [E3]) Is Nothing Then

Apologies

Bob

"Bob Phillips" <bob.ph...@tiscali.co.uk> wrote in message
news:e3bjI9PL...@TK2MSFTNGP09.phx.gbl...

[Barney Fife]

On Sat, 7 Jun 2003 15:38:28 +0100, "Bob Phillips"
<bob.ph...@tiscali.co.uk> wrote:

>Barney,
>
>small error
> If Not Intersect(Target = [E3]) Is Nothing Then
>should read
> If Not Intersect(Target, [E3]) Is Nothing Then
>
>Apologies
>

Thanks for the help. I never understood exactly how that "target
address" worked.