Very Basic Stock Screening VBA Code -Essential for Stock Traders-

[Trader11]

Hello,

I need help on a VBA code -which I think is a very basic one.

I have cells that display, "BUY", "SELL" or "-" based on some conditions. I would like to create a VBA code so that a corresponding cell would show the date when the signals i.e. "BUY", "SELL" or "-" are triggered for the given cell. When the signal disappears (due to change in the linked condition) the date should disappear as well. As some might have guessed this is for Stock Screening hence I have some hundred rows.

Also I've saw threads that read, "copy this code and paste it to the screen where it says 'view code' and then it should run automatically", for example, but I'm struggling with that. Will I need to click on "Run" function? And how will I apply this code on the column I like.

Thank you so much in advance.

 

[alan.m]

Hi Trader11,
I suggest to use the change event, so the code needs to go in the module for the worksheet
(While in Excel proper, right click on the Worksheet name tab and choose View Code)
you can try this code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  If Target.Column = 1 Then
    ThisRow = Target.Row
    If UCase(Target.Value) = "BUY" Or UCase(Target.Value) = "SELL" Then
      Range("b" & ThisRow) = Format(Now(), "short date")
    Else
      Range("B" & ThisRow) = ""
    End If
  End If
End Sub

You will need to change the target column in
If Target.Column = 1 Then
from 1 to whatever column your BUY & SELL are in
and change the b from Range("b" ...
to whatever letter column number you date should go in
HTH

 

[Trader11]

Alan,

Thanks for your prompt reply. I just tried this at the office today, but couldn't work it out. So the "BUY" or "SELL" column starts from cell Z3 and goes down. And I want my date to start from AA3 -adjacent cell.

So I did as follows

If Target.Column = 1 Then (I deleted 1 and written Z)
and
b from Range("b" ... (Changed "b" to AA)

I then saved the spreadsheet as macroenabled (.xlsm) and closed the VBA window.

When I open the file again nothing is different.

I clearly miss something, what should I do?

Thanks a lot.

PS: I use Office 2007

 

[alan.m]

Alan,

Thanks for your prompt reply. I just tried this at the office today, but couldn't work it out. So the "BUY" or "SELL" column starts from cell Z3 and goes down. And I want my date to start from AA3 -adjacent cell.

So I did as follows

If Target.Column = 1 Then (I deleted 1 and written Z)
and
b from Range("b" ... (Changed "b" to AA)

I then saved the spreadsheet as macroenabled (.xlsm) and closed the VBA window.

When I open the file again nothing is different.

I clearly miss something, what should I do?

Thanks a lot.

PS: I use Office 2007

Hi Trader,
Firstly for Target.Column = Z,
Target.Column = 26
Secondly, did you include quotes:
b from Range("b" ... (Changed "b" to AA)
Third, you didn't say where you placed the macro. Did you follow '(While in Excel proper, right click on the Worksheet name tab and choose View Code)' or did you put the macro in a module?

When it's done, it will work right away, without having to close & reopen the workbook
HTH

 

[Trader11]

I open the spreadsheet, I clicked on the name tab down below and paste code on the screen. the above two cells read "Worksheet" and "Change".

Below is the code I have.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 26 Then
ThisRow = Target.Row
If UCase(Target.Value) = "BUY" Or UCase(Target.Value) = "SELL" Then
Range("AA" & ThisRow) = Format(Now(), "short date")
Else
Range("AA" & ThisRow) = ""
End If
End If
End Sub

I didn't place any macro -I guess.

Anyway, when I paste the code nothing changes. :/

 

[Trader11]

Also, all my data is in a table I created to sort any data (ascending/descending), would that prevent anything?

 

[alan.m]

Having data in a table is expected, that's what worksheets are.
Sorting may trigger a change event, may trigger the macro to run.
The macro should run when you change a cell in Z, and you should see something in AA change.
I copied and pasted your code and it woks just like that for me.
I'm using Office 2010, but it shouldn't matter.
HTH

 

[Trader11]

Yes, it worked; when I changed a cell under Z column by hand the date appeared.

But should I be sure that if, from tomorrow onwards, a cell under column Z changes automatically based on conditions it is linked to, will the cell adjacent to it show the date?

 

[Trader11]

Because the problem is: when I change a cell linked to a condition so that the a recommendation cell turns "BUY", the date doens't appear.

Date only appears when I change the cell to "BUY" by hand.

 

[Peter_SSs]

Worksheet_Change code is not triggered by a cell changing if that cell contains a formula.