How to update an entire column with IF statements in VBA

[susan]

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

 

[avveerkar]

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]

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

 

[avveerkar]

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