I have this code and it is working very well when I fill cells manually

Private Sub Worksheet_Change(ByVal Target As Range)
If Selection.Count > 1 Then Exit Sub
If Not Intersect(Target, Columns("L:L")) Is Nothing Then
If Target <= 0 Or Not IsNumeric(Target) Then Exit Sub
(If Target > Target.Offset(0, -10) Then Target.Offset(0, 3) =
Target.Offset(0, 3) + Target.Offset(0, -9
(If Target < Target.Offset(0, -10) Then Target.Offset(0, 4) =
Target.Offset(0, 4) + Target.Offset(0, -9
( If Target = Target.Offset(0, -10) Then Target.Offset(0, 5) =
Target.Offset(0, 5) + Target.Offset(0, -9
End If
End Sub

But when I use auto fill for cells or if you make for example L7 = F4 and I
fill F4 with any number say 10 I can see number 10 automatically on L7 but no
result on Q,P,O which mean the code is not respond , how can I make the code
work with the auto fill.
I remember Dave Peterson has solved a problem similar to this one any one
can help.
Thank you.
Forcast

Just like that other thread, you're going to have to look at the cells that
changed in column L.

Dim myRng as range
dim myCell as range
set myrng = intersect(target,me.range("L:L"))

if myrng is nothing then
exit sub
end if

for each mycell in myrng.cells
'instead of using target.value and target.offset(...,
'use mycell
'and since you're changing the worksheet, you'll want to turn off
'events so that the _change event doesn't fire again (and again and again!)

application.enableevents = false
'your mycell... code here
application.enableevents = true
Next mycell





forcast wrote:
>
> I have this code and it is working very well when I fill cells manually
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Selection.Count > 1 Then Exit Sub
> If Not Intersect(Target, Columns("L:L")) Is Nothing Then
> If Target <= 0 Or Not IsNumeric(Target) Then Exit Sub
> (If Target > Target.Offset(0, -10) Then Target.Offset(0, 3) =
> Target.Offset(0, 3) + Target.Offset(0, -9
> (If Target < Target.Offset(0, -10) Then Target.Offset(0, 4) =
> Target.Offset(0, 4) + Target.Offset(0, -9
> ( If Target = Target.Offset(0, -10) Then Target.Offset(0, 5) =
> Target.Offset(0, 5) + Target.Offset(0, -9
> End If
> End Sub
>
> But when I use auto fill for cells or if you make for example L7 = F4 and I
> fill F4 with any number say 10 I can see number 10 automatically on L7 but no
> result on Q,P,O which mean the code is not respond , how can I make the code
> work with the auto fill.
> I remember Dave Peterson has solved a problem similar to this one any one
> can help.
> Thank you.
> Forcast

--

Dave Peterson

Dear Dave Peterson
How are you doing , you helped me a lot I appreciate your help , I don’t
understand very much the code in fact I have it ready so I couldn't change
it as you recommend would you please rewrite the code to fill my need and
this my parameter cell A = price , cell B = quantity, cell C = open . now I
want to add cell B to cell D (group 1) in case if A>C
I want to add cell B to cell E (group 2) in case if A<C
I want to add cell B to cell F (group 3) in case if A=C
I need the total in cells D,E,F accumulative .
Thank you for your help.
Regards
price quantity open group 1 group 2 group 3
=B+D if A>C =B+E if A<C =B+E if A=C




"Dave Peterson" wrote:

> Just like that other thread, you're going to have to look at the cells that
> changed in column L.
>
> Dim myRng as range
> dim myCell as range
> set myrng = intersect(target,me.range("L:L"))
>
> if myrng is nothing then
> exit sub
> end if
>
> for each mycell in myrng.cells
> 'instead of using target.value and target.offset(...,
> 'use mycell
> 'and since you're changing the worksheet, you'll want to turn off
> 'events so that the _change event doesn't fire again (and again and again!)
>
> application.enableevents = false
> 'your mycell... code here
> application.enableevents = true
> Next mycell
>
>
>
>
>
> forcast wrote:
> >
> > I have this code and it is working very well when I fill cells manually
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Selection.Count > 1 Then Exit Sub
> > If Not Intersect(Target, Columns("L:L")) Is Nothing Then
> > If Target <= 0 Or Not IsNumeric(Target) Then Exit Sub
> > (If Target > Target.Offset(0, -10) Then Target.Offset(0, 3) =
> > Target.Offset(0, 3) + Target.Offset(0, -9
> > (If Target < Target.Offset(0, -10) Then Target.Offset(0, 4) =
> > Target.Offset(0, 4) + Target.Offset(0, -9
> > ( If Target = Target.Offset(0, -10) Then Target.Offset(0, 5) =
> > Target.Offset(0, 5) + Target.Offset(0, -9
> > End If
> > End Sub
> >
> > But when I use auto fill for cells or if you make for example L7 = F4 and I
> > fill F4 with any number say 10 I can see number 10 automatically on L7 but no
> > result on Q,P,O which mean the code is not respond , how can I make the code
> > work with the auto fill.
> > I remember Dave Peterson has solved a problem similar to this one any one
> > can help.
> > Thank you.
> > Forcast
>
> --
>
> Dave Peterson
>

I'm sorry.

I don't understand enough to help.

You may want to take a crack at what you want and post back with any specific
questions you have.

forcast wrote:
>
> Dear Dave Peterson
> How are you doing , you helped me a lot I appreciate your help , I don’t
> understand very much the code in fact I have it ready so I couldn't change
> it as you recommend would you please rewrite the code to fill my need and
> this my parameter cell A = price , cell B = quantity, cell C = open . now I
> want to add cell B to cell D (group 1) in case if A>C
> I want to add cell B to cell E (group 2) in case if A<C
> I want to add cell B to cell F (group 3) in case if A=C
> I need the total in cells D,E,F accumulative .
> Thank you for your help.
> Regards
> price quantity open group 1 group 2 group 3
> =B+D if A>C =B+E if A<C =B+E if A=C
>
> "Dave Peterson" wrote:
>
> > Just like that other thread, you're going to have to look at the cells that
> > changed in column L.
> >
> > Dim myRng as range
> > dim myCell as range
> > set myrng = intersect(target,me.range("L:L"))
> >
> > if myrng is nothing then
> > exit sub
> > end if
> >
> > for each mycell in myrng.cells
> > 'instead of using target.value and target.offset(...,
> > 'use mycell
> > 'and since you're changing the worksheet, you'll want to turn off
> > 'events so that the _change event doesn't fire again (and again and again!)
> >
> > application.enableevents = false
> > 'your mycell... code here
> > application.enableevents = true
> > Next mycell
> >
> >
> >
> >
> >
> > forcast wrote:
> > >
> > > I have this code and it is working very well when I fill cells manually
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > If Selection.Count > 1 Then Exit Sub
> > > If Not Intersect(Target, Columns("L:L")) Is Nothing Then
> > > If Target <= 0 Or Not IsNumeric(Target) Then Exit Sub
> > > (If Target > Target.Offset(0, -10) Then Target.Offset(0, 3) =
> > > Target.Offset(0, 3) + Target.Offset(0, -9
> > > (If Target < Target.Offset(0, -10) Then Target.Offset(0, 4) =
> > > Target.Offset(0, 4) + Target.Offset(0, -9
> > > ( If Target = Target.Offset(0, -10) Then Target.Offset(0, 5) =
> > > Target.Offset(0, 5) + Target.Offset(0, -9
> > > End If
> > > End Sub
> > >
> > > But when I use auto fill for cells or if you make for example L7 = F4 and I
> > > fill F4 with any number say 10 I can see number 10 automatically on L7 but no
> > > result on Q,P,O which mean the code is not respond , how can I make the code
> > > work with the auto fill.
> > > I remember Dave Peterson has solved a problem similar to this one any one
> > > can help.
> > > Thank you.
> > > Forcast
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Dear Dave Peterson
I am working with the share stock ,the market open with specific price for
each company , I want to collect all the quantity 0f stock that has been
sold higher than the opening price for each company and put them in one cell
say for example cell E (group 1) and collect all the stock that has been sold
lower than the opening price for each company and put them in one cell say
for example cell F (group 2) and last collect all the stock that has been
sold with same as the opening price for each company and put them in one
cell say for example cell G(group 3) as shown below :
A B C D E F G
Company name Opening price Last price Last qua Group 1 Group2 Group 3
Central Bank 50 49 100 =F+100
Insurance CO. 30 32 500 =E+500
Petrochemical CO. 70 70 1200 =G+1200
Phrmatical CO. 60 59 800 =F+800
off course the opening price stay fix all the day but last price and last
quantity change every second or so ,I am comparing the last price with open
price if the last price higher then I add last quantity to the value of cell
E if less I add last quantity to the value of cell F and if the last price
equal to the open price I add last quantity to the value of cell G and this
is the code it is working fine but if I insert the last price manually , and
stop and doesn't respond if I make for example C1=T10 as in our previous
problem .

If Selection.Count > 1 Then Exit Sub
If Not Intersect(Target, Columns("C:C")) Is Nothing Then
If Target <= 0 Or Not IsNumeric(Target) Then Exit Sub
If Target > Target.Offset(0, -1) Then Target.Offset(0, 2) =
Target.Offset(0, 2) + Target.Offset(0, 1)
If Target < Target.Offset(0, -1) Then Target.Offset(0, 3) =
Target.Offset(0, 3) + Target.Offset(0, 1)
If Target = Target.Offset(0, -1) Then Target.Offset(0, 4) =
Target.Offset(0, 4) + Target.Offset(0, 1)
End If
End Sub


"Dave Peterson" wrote:

> I'm sorry.
>
> I don't understand enough to help.
>
> You may want to take a crack at what you want and post back with any specific
> questions you have.
>
> forcast wrote:
> >
> > Dear Dave Peterson
> > How are you doing , you helped me a lot I appreciate your help , I don’t
> > understand very much the code in fact I have it ready so I couldn't change
> > it as you recommend would you please rewrite the code to fill my need and
> > this my parameter cell A = price , cell B = quantity, cell C = open . now I
> > want to add cell B to cell D (group 1) in case if A>C
> > I want to add cell B to cell E (group 2) in case if A<C
> > I want to add cell B to cell F (group 3) in case if A=C
> > I need the total in cells D,E,F accumulative .
> > Thank you for your help.
> > Regards
> > price quantity open group 1 group 2 group 3
> > =B+D if A>C =B+E if A<C =B+E if A=C
> >
> > "Dave Peterson" wrote:
> >
> > > Just like that other thread, you're going to have to look at the cells that
> > > changed in column L.
> > >
> > > Dim myRng as range
> > > dim myCell as range
> > > set myrng = intersect(target,me.range("L:L"))
> > >
> > > if myrng is nothing then
> > > exit sub
> > > end if
> > >
> > > for each mycell in myrng.cells
> > > 'instead of using target.value and target.offset(...,
> > > 'use mycell
> > > 'and since you're changing the worksheet, you'll want to turn off
> > > 'events so that the _change event doesn't fire again (and again and again!)
> > >
> > > application.enableevents = false
> > > 'your mycell... code here
> > > application.enableevents = true
> > > Next mycell
> > >
> > >
> > >
> > >
> > >
> > > forcast wrote:
> > > >
> > > > I have this code and it is working very well when I fill cells manually
> > > >
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > If Selection.Count > 1 Then Exit Sub
> > > > If Not Intersect(Target, Columns("L:L")) Is Nothing Then
> > > > If Target <= 0 Or Not IsNumeric(Target) Then Exit Sub
> > > > (If Target > Target.Offset(0, -10) Then Target.Offset(0, 3) =
> > > > Target.Offset(0, 3) + Target.Offset(0, -9
> > > > (If Target < Target.Offset(0, -10) Then Target.Offset(0, 4) =
> > > > Target.Offset(0, 4) + Target.Offset(0, -9
> > > > ( If Target = Target.Offset(0, -10) Then Target.Offset(0, 5) =
> > > > Target.Offset(0, 5) + Target.Offset(0, -9
> > > > End If
> > > > End Sub
> > > >
> > > > But when I use auto fill for cells or if you make for example L7 = F4 and I
> > > > fill F4 with any number say 10 I can see number 10 automatically on L7 but no
> > > > result on Q,P,O which mean the code is not respond , how can I make the code
> > > > work with the auto fill.
> > > > I remember Dave Peterson has solved a problem similar to this one any one
> > > > can help.
> > > > Thank you.
> > > > Forcast
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Maybe someone else can figure this out. I'm still having trouble.

forcast wrote:
>
> Dear Dave Peterson
> I am working with the share stock ,the market open with specific price for
> each company , I want to collect all the quantity 0f stock that has been
> sold higher than the opening price for each company and put them in one cell
> say for example cell E (group 1) and collect all the stock that has been sold
> lower than the opening price for each company and put them in one cell say
> for example cell F (group 2) and last collect all the stock that has been
> sold with same as the opening price for each company and put them in one
> cell say for example cell G(group 3) as shown below :
> A B C D E F G
> Company name Opening price Last price Last qua Group 1 Group2 Group 3
> Central Bank 50 49 100 =F+100
> Insurance CO. 30 32 500 =E+500
> Petrochemical CO. 70 70 1200 =G+1200
> Phrmatical CO. 60 59 800 =F+800
> off course the opening price stay fix all the day but last price and last
> quantity change every second or so ,I am comparing the last price with open
> price if the last price higher then I add last quantity to the value of cell
> E if less I add last quantity to the value of cell F and if the last price
> equal to the open price I add last quantity to the value of cell G and this
> is the code it is working fine but if I insert the last price manually , and
> stop and doesn't respond if I make for example C1=T10 as in our previous
> problem .
>
> If Selection.Count > 1 Then Exit Sub
> If Not Intersect(Target, Columns("C:C")) Is Nothing Then
> If Target <= 0 Or Not IsNumeric(Target) Then Exit Sub
> If Target > Target.Offset(0, -1) Then Target.Offset(0, 2) =
> Target.Offset(0, 2) + Target.Offset(0, 1)
> If Target < Target.Offset(0, -1) Then Target.Offset(0, 3) =
> Target.Offset(0, 3) + Target.Offset(0, 1)
> If Target = Target.Offset(0, -1) Then Target.Offset(0, 4) =
> Target.Offset(0, 4) + Target.Offset(0, 1)
> End If
> End Sub
>
> "Dave Peterson" wrote:
>
> > I'm sorry.
> >
> > I don't understand enough to help.
> >
> > You may want to take a crack at what you want and post back with any specific
> > questions you have.
> >
> > forcast wrote:
> > >
> > > Dear Dave Peterson
> > > How are you doing , you helped me a lot I appreciate your help , I don’t
> > > understand very much the code in fact I have it ready so I couldn't change
> > > it as you recommend would you please rewrite the code to fill my need and
> > > this my parameter cell A = price , cell B = quantity, cell C = open . now I
> > > want to add cell B to cell D (group 1) in case if A>C
> > > I want to add cell B to cell E (group 2) in case if A<C
> > > I want to add cell B to cell F (group 3) in case if A=C
> > > I need the total in cells D,E,F accumulative .
> > > Thank you for your help.
> > > Regards
> > > price quantity open group 1 group 2 group 3
> > > =B+D if A>C =B+E if A<C =B+E if A=C
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Just like that other thread, you're going to have to look at the cells that
> > > > changed in column L.
> > > >
> > > > Dim myRng as range
> > > > dim myCell as range
> > > > set myrng = intersect(target,me.range("L:L"))
> > > >
> > > > if myrng is nothing then
> > > > exit sub
> > > > end if
> > > >
> > > > for each mycell in myrng.cells
> > > > 'instead of using target.value and target.offset(...,
> > > > 'use mycell
> > > > 'and since you're changing the worksheet, you'll want to turn off
> > > > 'events so that the _change event doesn't fire again (and again and again!)
> > > >
> > > > application.enableevents = false
> > > > 'your mycell... code here
> > > > application.enableevents = true
> > > > Next mycell
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > forcast wrote:
> > > > >
> > > > > I have this code and it is working very well when I fill cells manually
> > > > >
> > > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > > If Selection.Count > 1 Then Exit Sub
> > > > > If Not Intersect(Target, Columns("L:L")) Is Nothing Then
> > > > > If Target <= 0 Or Not IsNumeric(Target) Then Exit Sub
> > > > > (If Target > Target.Offset(0, -10) Then Target.Offset(0, 3) =
> > > > > Target.Offset(0, 3) + Target.Offset(0, -9
> > > > > (If Target < Target.Offset(0, -10) Then Target.Offset(0, 4) =
> > > > > Target.Offset(0, 4) + Target.Offset(0, -9
> > > > > ( If Target = Target.Offset(0, -10) Then Target.Offset(0, 5) =
> > > > > Target.Offset(0, 5) + Target.Offset(0, -9
> > > > > End If
> > > > > End Sub
> > > > >
> > > > > But when I use auto fill for cells or if you make for example L7 = F4 and I
> > > > > fill F4 with any number say 10 I can see number 10 automatically on L7 but no
> > > > > result on Q,P,O which mean the code is not respond , how can I make the code
> > > > > work with the auto fill.
> > > > > I remember Dave Peterson has solved a problem similar to this one any one
> > > > > can help.
> > > > > Thank you.
> > > > > Forcast
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson