 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2008the code is not respond
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 > I have this code and it is working very well when I fill cells manually > [quoted text clipped - 19 lines] > Thank you. > Forcast  SignatureDave 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 > Just like that other thread, you're going to have to look at the cells that > changed in column L. [quoted text clipped - 41 lines] > > Thank you. > > Forcast 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. > Dear Dave Peterson > How are you doing , you helped me a lot I appreciate your help , I donât [quoted text clipped - 59 lines] > > > > Dave Peterson SignatureDave 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 > I'm sorry. > [quoted text clipped - 66 lines] > > > > > > Dave Peterson Maybe someone else can figure this out. I'm still having trouble. > Dear Dave Peterson > I am working with the share stock ,the market open with specific price for [quoted text clipped - 106 lines] > > > > Dave Peterson SignatureDave Peterson
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.