 |
 | 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 / Programming / January 2006How to update an entire column with IF statements in VBA
I am trying to do the following with 4 if statements to reflect the status of individual orders: Column H = Quantity Start (manually entered) Column P = Quantity Filled (manually entered) Column S = Quantity Remaining (formula driven ie. Column H - P) Column C = Status (formula driven by the 4 IF statements shown below) Column O = Expiry Date (manually entered) Column C is the dependent variable showing the status as to whether an order is (1)open; (2) filled; (3) partially filled; or (4) expired/historical depending on what is entered in Column's H,O,P,S Eg. Assume today is Jan. 25 (Status) (Quantity Start) (Expiry Date) (Quantity Filled) (Quantity Remaining) Column C Column H Column O Column P Column S Open 2000 Jan. 26 2000 Historical 2000 Jan.24 2000 Filled 5000 Jan. 25 5000 0 Partial 5000 Jan. 25 2000 3000 " " " " " " " " " " " " " " " " " " " " " Note: mytime = now I made it so orders expire at noon on the expiry date, i.e., change from open to historical if orders are not filled or partially filled. If Range("H" & Target.Row).Value = Range("P" & Target.Row).Value And _ Range("S" & Target.Row).Value = 0 Then Range("C" & Target.Row).Value = "Filled" ElseIf Range("H" & Target.Row).Value > Range("P" & Target.Row).Value And _ Range("P" & Target.Row).Value <> 0 Then Range("C" & Target.Row).Value = "Partial" ElseIf mytime > Range("O" & Target.Row).Value + 0.5 And Range("P" & Target.Row).Value = 0 And _ Range("H" & Target.Row).Value = Range("S" & Target.Row).Value Then Range("C" & Target.Row).Value = "Historical" ElseIf Range("H" & Target.Row).Value = Range("S" & Target.Row).Value And _ Range("P" & Target.Row).Value = 0 Then Range("C" & Target.Row).Value = "Open" End If The difficulty I'm having however is in getting the Status (Column C) for open orders to change to historical through out the entire (Column C range) when the expiration date/time is elapsed. Eg.1 Assume today is Jan. 25 at 11:30 am (Status) (Quantity Start) (Expiry Date) (Quantity Filled) (Quantity Remaining) Column C Column H Column O Column P Column S Open 200 Jan. 25 200 Open 500 Jan. 25 500 Open 2000 Jan. 26 2000 Open 800 Jan. 25 800 Eg.2 Assume today is Jan. 25 at 12:01 pm (31 minutes later) (Status) (Quantity Start) (Expiry Date) (Quantity Filled) (Quantity Remaining) Column C Column H Column O Column P Column S Historical 200 Jan. 25 200 Historical 500 Jan. 25 500 Open 2000 Jan. 26 2000 Historical 800 Jan. 25 800 Assume that one end user entered 4 orders (11:30 am) as in Eg. 1. Assume another end user is about to enter an additional order (12:01 pm), how can I make the status change as in Eg. 2 when they click on any cell on the speadsheet. In other words what I'm missing is what stimulates the entire C Column to accurately update all the status', just by clicking on any cell. Thank you in advance. Susan Are you looking for an event to execute your procedure? Worksheet_change is one such method. But you are already using Target object ( eg Target.row )and I am assuming you are using a change event such as Sub Worksheet_Change(ByVal Target as Range) to get the target and execute the procedure. Procedure will work fine but it will only update only that row in which the user clicks a cell and not entire column. Is that your problem? That it only updates one row and not entire column? Your procedure will update Status only in the row in which the user clicks any cell. Or your problem is that it does not update Status at all because you could not locate an even method ( such as clicking on a cell ) to execute the procedure? Then Worksheet_Change(ByVal Target as Range) is one you could look at. A V Veerkar susan Wrote: > I am trying to do the following with 4 if statements to reflect the > status of individual orders: [quoted text clipped - 87 lines] > > Susan  Signatureavveerkar Hi It only updates one row (the row the user clicks in) and not the entire column is the problem. I would like to update the entire 'status' column when the user clicks on any cell between columns A to Z. Any help is greatly appreciated Thanks Susan >Are you looking for an event to execute your procedure? Worksheet_change >is one such method. But you are already using Target object ( eg [quoted text clipped - 103 lines] >> >> Susan Hi Susan, Sorry I was away from my PC for some time hence the delay. Though not very efficient but we could write your proc without making many changes ( I am assuming that you have 100 rows starting from 1. If not you will need to make changes in the first line For .... And I am changing Target.Row to TargetRow so that it only becomes a variant and not method returning row number) For TargetRow = 1 to 100 If Range("H" & TargetRow).Value = Range("P" & TargetRow).Value And _ Range("S" & TargetRow).Value = 0 Then Range("C" & TargetRow).Value = "Filled" ElseIf Range("H" & TargetRow).Value > Range("P" & TargetRow).Value And _ Range("P" & TargetRow).Value <> 0 Then Range("C" & TargetRow).Value = "Partial" ElseIf mytime > Range("O" & TargetRow).Value + 0.5 And Range("P" & TargetRow).Value = 0 And _ Range("H" & TargetRow).Value = Range("S" & TargetRow).Value Then Range("C" & TargetRow).Value = "Historical" ElseIf Range("H" & TargetRow).Value = Range("S" & TargetRow).Value And _ Range("P" & TargetRow).Value = 0 Then Range("C" & TargetRow).Value = "Open" End If Next Now TargetRow is only variable ( instead of TargetRow you could write i) which updates from 1 to 100 successively in step of 1 as the For loop executes 100 times. Suggestion. A better method would be to use OnTime method. You could have one procedure say Sub Schedule_updt Application.OnTime TimeValue("12:00:00"), "my_Procedure" End Sub This will automatically run your procedure ( my_Procedure ) at 12 without any need for user to click Good luck A V Veerkar susan Wrote: > Hi > It only updates one row (the row the user clicks in) and not the entire [quoted text clipped - 128 lines] > >> > >> Susan Signatureavveerkar |
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.