PDA

View Full Version : Newbie Help with VBA I think

garretttpe
02-25-2015, 01:35 AM
Hi All
I have a spread sheet that tracks stocks and I need help. I need to be able to find the next sell date after a purchase and then recalculate after each find. Below is an example. On 1/2/15 the open v high was .10 cents therefore a sell would have taken place at each trigger, 0.02,0.03,0.04,0.05,.0.06, and 0.07 cents. On 1/5/15 there was only a .06 cent difference between open and high so a trigger at 0.07 would not have taken place that day and al others would have. this were is where I get lost. I need the formula to look at the open close low and high prices on the entire sheet (will be 255 rows) until it finds 7.63+.07 and put the value under the 0.07 column across from the date the trigger was found. in this case 2 days later. I need for this to happen continually for 255 rows. amytime a trade would have taken place I need a value of zero or leave the cell blank.
I have added my spreadsheet. You will see the sheet with the data and the next sheet is what I need it to look like and compute automatically.

Thank you all for any help on this. been kicking my but for 2 weeks straight.

Date
Open
High
Low
Close
open V high
Shares
Daily \$\$
Daily \$\$
Daily \$\$
Daily \$\$
Daily \$\$
Daily \$\$

If sold
If sold
If sold
If sold
If sold
If sold

\$0.02
\$0.03
\$0.04
\$0.05
\$0.06
\$0.07

1/2/2015
7.65
7.75
7.5
7.63
0.10
3922
\$78.04
\$117.25
\$156.47
\$195.69
\$234.90
\$274.12

1/5/2015
7.63
7.69
7.46
7.52
0.06
3932
\$78.24
\$117.56
\$156.88
\$196.20
\$235.52

1/6/2015
7.6
7.72
7.48
7.71
0.12
3947
\$78.55
\$118.03
\$157.50
\$196.97
\$236.45
\$275.23

1/7/2015
7.78
7.8
7.68
7.76
0.02
3856
\$76.74

\$0.00

1/8/2015
7.82
7.9
7.79
7.85
0.08
3836
\$76.34
\$115.68
\$154.24
\$192.80

\$268.16

1/9/2015
7.85
7.88
7.73
7.83
0.03
3822
\$76.05
\$114.27

1/12/2015
7.85
7.86
7.65
7.79
0.01
3822

Yongle
02-26-2015, 02:44 PM
I read your post three times and still do not understand the question.
1 What exactly triggers next sale after purchase?
2 Are you only interested in the 0.07 column?

garretttpe
02-26-2015, 10:10 PM
Good Morning and thank you for responding.
1. B4 is 7.65, F4 is .10 there for h4,i4,j4,k4,l4,m4, would be triggered as those are all less then F4 and the result is in those cells.
2. B5 is 7.63, f5 is.06 so only H5,i5,j5,k5,l5 would be triggered and M5 would not as .07 is greater then F5.
3. now I need to find when B5+.07(7.70) would have taken place within \$b6\$E255. and place the value (B5+.07)*G5 in the .07 column when that date is triggered.
4. I am interested in each of the H, I, J, K, L, M cells to do this.
I need to be able to see when a sell will take place based on the difference of open price and high price after a sell. then the next day will be a buy at open and then look again for a sell trigger, anytime H,i,j,k,l,m is greater then its the open to high difference.

I hope I am explaining this ok if not please let me know and I will try again.

Larry

Yongle
02-27-2015, 06:30 AM
Ok - nearly there, I think.

Hi Larry - I think I now understand, thank you for your patience.

Question 1 - is all this correct?
Each day if colF > 0.07 then calculate all values in columns H to M at 0.02 to 0.07

If colF < 0.07, value in Column M = blank (this happens on Jan 05)
If colF < 0.06, value in Columns L to M = blank
If colF < 0.05, value in Columns K to M = blank etc

On Jan 05, cannot sell at 0.07, check for next possible sell. When is price next > 7.70 (Jan 05 Open + 0.07)
Jan 06 High = 7.72 (which is > 7.70) so this is next sell and we put G5 X .07 into cell M6 (because we bought them at open price B5

Question 2 - (Please answer this question ONLY if Question 1 is correct)
On Jan 06 value in column F = 0.12 which > 0.07 and all conditions for Jan 06 are satisfied, so cell M6 would be G6 X 0.07

Which is correct ?
M6= (G5 X 0.07) OR
M6 =( G5 X 0.07) + (G6 X 0.07) which allows for selling Jan 06 and Jan 05 shares at 0.07 on this day

Yon

garretttpe
02-27-2015, 07:01 AM
Hi Yon
1 is correct
2. Almost there, Jan 5th condition for M5 was meet on Jan 6th due to C6 being greater than (b5+0.07) not F6 being >0.07. which allows selling G5 shares at 0.07 and value in M6

I cannot thank you enough for working through this with me.
Larry

Yongle
02-27-2015, 07:37 AM
Ok Larry

So now to Question 2
On Jan 06 value in column F = 0.12 which > 0.07 and all conditions for Jan 06 are satisfied, so cell M6 would be G6 X 0.07

Which is correct ?
M6= (G5 X 0.07) OR
M6 =( G5 X 0.07) + (G6 X 0.07) which allows for selling Jan 06 and Jan 05 shares at 0.07 on this day

Yon

garretttpe
02-27-2015, 09:17 AM
Hi Yon
Looks like M6=(G5 x 0.07)
thanks again

Larry

Yongle
02-27-2015, 10:15 AM
Ok - that is fine. I understand now.

Last question - what happens to Jan06 stock that would have been in cell M6 (=G6 X 0.07) - do we just ignore this?

thanks
Yon

Yongle
02-28-2015, 05:23 AM
Hi Larry

I think you want something like this.

I have assumed that you are going to run this macro after all the values are already in the cells from your various formulas. What I did was to copy columns A to M to sheet1 and ran the code against that sheet - so you will need to amend the first line of code if you want to use a different sheet.
I was not sure if you wanted a formula in the cells being overwritten or values - I think you would probably prefer to see the formula - so that is what I have done.
The vba looks for Zero values in columns H to M and then looks for the first time that sell conditions are met and overwrites in correct cell in columns H to M

To make it a little easier to see what is going on with all those numbers, I have included 3 lines that you can remove once you are happy the code is doing what you want it to do.
- The zero value cells in rows H to M are highlighted and also cells amended by the next sell are highlighted in a different color.
- There is a comprehensive message box that informs you exactly where the values are coming from, so that it makes it easy to check the logic. The box will pop up every time a cell is being amended. This may be useful for the first few entries but very tedious after that. At the moment there is an apostrophe in front to stop it running. To switch it on just remove the apostrophe. When you get bored of seeing the messages you can hit ctrl + break to stop the code running. I found it very useful when I was testing the code.

One thing I have noticed is that that there are times when a previous cell overwrite is then overwritten again because the price on the day satisfies the conditions to trigger a sell more than once. If this is what you want the spreadsheet to do then that is fine. (It looks odd that these potential sells "disappear" - but I have treated them in the same manner as what we discussed yesterday.)

If anything is unclear or if you need any help amending anything please shout.
Any problems running the code and I could send your original workbook which now incudes the VBA - but I am sure you would rather have a go yourself first. I hope this helps you make a big profit an selling stock. / :giggle
Yon

Sub NextSell()

Sheets("Sheet1").Select
'set up variables
Dim Shares As String, Margin As String, CellRef As String
Dim r As Integer, c As Integer

For r = 4 To 255
For c = 8 To 13

If Cells(r, 2).Value = 0 Then Exit Sub 'ignore later cells with no purchases
If Cells(r, c) = 0 Then
' highlight cells where no sales
Cells(r, c).Interior.Color = RGB(250, 200, 150) 'Can delete this line
Price = Cells(r, 2).Value
MinSellPrice = Price + Cells(3, c).Value
For r2 = r + 1 To 255
If Cells(r2, 3) >= MinSellPrice Then
'highlight cells where condition is met for first time
Cells(r2, c).Interior.Color = RGB(200, 200, 200) ''Can delete this line
Shares = Cells(r, 7).Address
Margin = Cells(3, c).Address

'Can delete the next 4 lines
'MsgBox Format(Range(Shares).Value, "0,000") & " Shares" & vbNewLine _
& "Bought on " & Cells(r, 1).Value & vbNewLine & "Cost \$" & Format(Price, "0.00") & vbNewLine & _
"Margin " & Range(Margin).Value & vbNewLine & "Minimum selling price \$ " _
& Format(MinSellPrice, "0.00") & vbNewLine & "Achieved on " & Cells(r2, 1).Value

Cells(r2, c).Select
'put formula in cells when stock can be sold
CellRef = "=" & Shares & "*" & Margin
ActiveCell.Formula = CellRef
r2 = 255
Else
End If
Next r2
Else
End If
Next c
Next r

End Sub

garretttpe
02-28-2015, 11:47 PM
Good Morning Sir
Sorry for the delay getting back to you, my work has me straight out sometimes. I cant thank you enough for the time and help you have given me, a complete stranger. I have one more important question. How the heck do I get this to run?? I have excel 2010, I opened VBA and copied the formula to sheet 1, then when I try to run Macro it says script out of range. Is there anyway you can send me the file with everything running. I am afraid when it comes to this stuff I am pretty much lost. I am ok with excel but this is way over my head.

Larry

Yongle
03-01-2015, 12:45 AM
Hi Larry :hi:

Here is the file.
When you open click to enable macros and then hit ctrl + shift + s to run the macro

The macro is currently set to run with the message pop-up - I think you will find that useful. Like I said (previous post) when you get bored of clicking OK, use ctrl + break to stop the code running. And then remove the apostrophe.

There is also a second macro which is useful - a quick eraser - this deletes everything in sheet1 and copies the original values back . A full reset is something that I find essential when testing code. With VBA if you change something and run things from where you last fiddled, it bites you in the butt sometime later when you run the code from a clean start. Anyway use ctrl + shift + v to rub everything out and start again. (no need to run this before running other macro, but may be useful if you are doing any other experimenting in sheet1

As to your questions - my own workbooks have been tamed long ago and are therefore now less interesting. I enjoy new puzzles. Your Excel requirements (and everyone else's) are different to mine. My VBA skills can only be honed by trying new code. VBA becomes a bit addictive. We all gained from the help of others when we started too, and those first tentative steps are absolutely the hardest.

Anyway - I hope the numbers come out the way you expect them to.
Yon

garretttpe
03-01-2015, 02:00 AM
Good morning Yon
I jumped the gun on my last email, I was able to get everything working. unfortunately there is still a issue with the CODE. if you look at the Rite Aid Result tab you will see what the results need to be. the code as it stands start to work then fills in sell triggers when no triggers are set.

Yongle
03-01-2015, 02:41 AM
Hi Larry
I am clearly being a bit slow on the uptake this morning, and I am sure it is absolutely nothing to do with that excellent ale that needed consuming urgently before it went stale last night.
You know what you expect in the end result, and I do not - I am looking hard but not seeing! So can you help me out and be very specific and then we will get pretty quickly.

If you start the code running with the message box enabled until it you hit your first problem then tell me which cell it is and why there should not be a trigger at that point. Can you check all the entries in the message box and confirm that all is ok there. Can you then keep keep hitting OK until the you get to the next 2 incorrect triggers and do the same.

Thanks
Yon

garretttpe
03-01-2015, 03:11 AM
Hi Yon

must be nice I am at a place where there is no alcohol. soon though I will be home in the states and then ale time it is.
1.M9 is a value and it should be zero. F9 is less then .07 so M9 must be zero
2.H11 needs to be zero. Reason- stock was bought on A10 due to sell on H9, no trigger on H10(F is less than .02) so the next open, high, low, close value that is A10=.02 is on D31. so I need H31 to be .02*g10.

Larry

Yongle
03-01-2015, 04:01 AM
Ok - will look at this after a typical Sunday lunchtime libation in honour of Bacchus, not that I am trying to make you jealous or anything...
Yon

Yongle
03-01-2015, 07:21 AM
Larry :yes
What you are seeing does not match what I am looking at. I have run the macro repeatedly and always get the same result but I have always cleared the data out and started afresh with the original data before re-running the macro whilst I was testing. There is a big clue in that sentence! I think we have got there.

Can you open the spreadsheet that I sent you in post#11 and run the macro from within that (using ctrl + shift + s).

Below is the output that I always get and looking at the 3 cells (in red) that you mentioned:
M9 (9 January) is zero - just what you expected
H10 (12 january) is zero - just what you expected
H31 (11 February) is G10 X H3 - just what you expected

Regards
Yon

Date
Open
High
Low
Close
open V high
Shares
Daily \$\$
Daily \$\$
Daily \$\$
Daily \$\$
Daily \$\$
Daily \$\$

If sold
If sold
If sold
If sold
If sold
If sold

\$0.02
\$0.03
\$0.04
\$0.05
\$0.06
\$0.07

02/01/2015
7.65
7.75
7.5
7.63
0.10
3922
\$78.04
\$117.25
\$156.47
\$195.69
\$234.90
\$274.12

05/01/2015
7.63
7.69
7.46
7.52
0.06
3932
\$78.24
\$117.56
\$156.88
\$196.20
\$235.52
\$0.00

06/01/2015
7.6
7.72
7.48
7.71
0.12
3947
\$78.55
\$118.03
\$157.50
\$196.97
\$236.45
\$275.23

07/01/2015
7.78
7.8
7.68
7.76
0.02
3856
\$76.74
\$0.00
\$0.00
\$0.00
\$0.00
\$0.00

08/01/2015
7.82
7.9
7.79
7.85
0.08
3836
\$76.34
\$115.68
\$154.24
\$192.80
\$231.36
\$269.92

09/01/2015
7.85
7.88
7.73
7.83
0.03
3822
\$76.05
\$114.27
\$0.00
\$0.00
\$0.00
\$0.00

12/01/2015
7.85
7.86
7.65
7.79
0.01
3822
\$0.00
\$0.00
\$0.00
\$0.00
\$0.00
\$0.00

13/01/2015
7.8
7.83
7.43
7.57
0.03
3846
\$76.54
\$115.00
\$0.00
\$0.00
\$0.00
\$0.00

14/01/2015
7.4
7.59
7.21
7.55
0.19
4054
\$80.68
\$121.22
\$161.76
\$202.30
\$242.84
\$283.38

15/01/2015
7.56
7.6
7.27
7.34
0.04
3968
\$78.97
\$118.65
\$158.33
\$0.00
\$0.00
\$0.00

16/01/2015
7.29
7.41
7.22
7.4
0.12
4115
\$81.89
\$123.05
\$164.20
\$205.35
\$246.50
\$287.65

20/01/2015
7.48
7.49
7.16
7.32
0.01
4011
\$0.00
\$0.00
\$0.00
\$0.00
\$0.00
\$0.00

21/01/2015
7.3
7.43
7.23
7.32
0.13
4110
\$81.78
\$122.88
\$163.97
\$205.07
\$246.16
\$287.26

22/01/2015
7.39
7.49
7.28
7.45
0.10
4060
\$80.78
\$121.38
\$161.98
\$202.57
\$243.17
\$283.76

23/01/2015
7.44
7.58
7.43
7.51
0.14
4032
\$80.21
\$120.32
\$160.43
\$200.53
\$240.64
\$280.75

26/01/2015
7.49
7.66
7.46
7.65
0.17
4005
\$79.71
\$119.76
\$159.81
\$198.41
\$238.10
\$277.78

27/01/2015
7.55
7.65
7.53
7.58
0.10
3974
\$79.07
\$118.81
\$158.54
\$198.28
\$238.01
\$277.75

28/01/2015
7.61
7.66
7.39
7.42
0.05
3942
\$78.45
\$117.87
\$157.29
\$196.71
\$236.14
\$0.00

29/01/2015
7.45
7.47
7.06
7.26
0.02
4027
\$80.13
\$0.00
\$0.00
\$0.00
\$0.00
\$0.00

30/01/2015
7.2
7.24
6.98
6.98
0.04
4167
\$82.92
\$124.58
\$166.25
\$0.00
\$0.00
\$0.00

02/02/2015
7
7.08
6.68
7.05
0.08
4286
\$85.29
\$128.14
\$171.00
\$213.86
\$256.71
\$299.57

03/02/2015
7.09
7.32
7.09
7.29
0.23
4231
\$84.20
\$126.52
\$168.83
\$208.33
\$250.00
\$291.67

04/02/2015
7.21
7.39
7.17
7.28
0.18
4161
\$82.80
\$124.41
\$166.02
\$207.63
\$249.24
\$290.85

05/02/2015
7.3
7.35
7.24
7.35
0.05
4110
\$81.78
\$122.88
\$163.97
\$205.07
\$246.16
\$0.00

06/02/2015
7.4
7.48
7.35
7.35
0.08
4054
\$80.68
\$120.81
\$161.76
\$202.30
\$242.84
\$287.67

09/02/2015
7.32
7.4
7.28
7.39
0.08
4098
\$81.56
\$122.54
\$163.52
\$204.51
\$245.49
\$286.48

10/02/2015
7.43
7.6
7.41
7.58
0.17
4038
\$80.35
\$120.73
\$161.07
\$201.34
\$241.61
\$281.88

11/02/2015
8.25
8.48
7.92
8.08
0.23
3636
\$76.43
\$114.65
\$153.85
\$192.31
\$230.77
\$275.95

12/02/2015
8.22
8.22
7.91
8.09
0.00
3650
\$0.00
\$0.00
\$0.00
\$0.00
\$0.00
\$0.00

13/02/2015
8.11
8.4
8.08
8.34
0.29
3699
\$72.99
\$109.49
\$145.99
\$182.48
\$218.98
\$255.47

17/02/2015
8.37
8.38
8.15
8.2
0.01
3584
\$0.00
\$0.00
\$0.00
\$0.00
\$0.00
\$0.00

18/02/2015
8.18
8.22
8.15
8.19
0.04
3667
\$72.98
\$109.66
\$146.33
\$0.00
\$0.00
\$0.00

19/02/2015
8.16
8.22
7.96
7.99
0.06
3676
\$73.16
\$109.93
\$146.69
\$183.46
\$220.22
\$0.00

20/02/2015
8.01
8.17
8
8.15
0.16
3745
\$74.53
\$111.99
\$149.44
\$186.89
\$224.34
\$261.80

garretttpe
03-01-2015, 07:54 AM
Hi Yon
yes the red indicators are correct but all cells in between need to be blank or 0.00 on 12/01/15 the open price was 7.85. it wasn't until 11/02/15 when a sell should be triggerd. 7.85+.02 (7.87) happened on the open so the red as indicated would be correct. all cells in between needs to be blank or 0.00.

thanks
Larry

Yongle
03-01-2015, 09:20 AM
Hi Larry

You may have you heard the expression "two nations divided by a common language" being applied to us in the UK and you guys over the pond. What an appropriate expression here!!
Here is an explanation of what the code is doing and hopefully you can tell me what you really want it to be doing, and this time I will do my very best to hear the same thing you are saying.

1 Your formulas calculates everything that go into columns H to M
2 The macro then looks at every cell H4 to M255 looking for value = 0 (as calculated by your formulas) and takes the Open price that day and adds the appropriate Column H to M margin to that to value before comparing it with the subsequent entries in the High price column until it finds a value that will satisfy the relevant condition.
3 When it finds that value, it then puts an entry in the relevant column (H to M) for that day overwriting what was there before
4 It does not make any difference to the result but it goes through that process starting at H4, then I4, J4 K4, L4 to M4 then onto row 5 etc
So far so good.....I think....
At this point every cell is either where your formulas left it, or as overwritten by the macro process explained above.
I'm happy, this is what I expected BUT you are puzzled, it is not what you expected.

So let's try one more time. What would you like the macro to do next?
Given our previous communication mismatch, it may be easier if you could describe it in the way a macro would approach it ( ie try to step through which value needs to be changed, and how that is determined - what it needs checking against, what is the condition that needs to be met and what the new value should be.)
And finally....maybe... but I somehow doubt that... - what is the difference between a blank cell and a zero value cell, and what makes that happen - at the moment the zero value cells are there because that is what your formula has put there and there is nothing in the macro to change anything to a blank.

I will be working this challenge out of my system during the next 2 -3 hours at the gym...
I look forward to your reply.
regards
Yon

garretttpe
03-01-2015, 09:49 AM
Hi Yon
I wil try my best again. a blank cell or 0.00 cell is the same to me. this is for column H result only .02 cents
On 12/1/15 a buy was done at 7.85 (B10), no sale at H10 (0.02) due to F10 being 0.01. Now I need to look at all the open, high, low and close prices from row 11 to 255 until 7.87 or greater is found (7.85+.02). that happened on ROW 31 (B31). Since I bought 3822 shares on 12/1/15 (B10) I need to multiply .02*3822 and the result needs to be in H31 and all cells from H10 and H30 will be 0.00 or blank don't matter to me.

eventually I need the same thing to happen in I, J, K, L, M, also with their perspective prices, .03,.04,.05,.06,.07.

I wont be back on until tomorrow AM. I am Temporarily in Jordan so I am not to far off time wise from you.

Thanks Again
Larry

Yongle
03-02-2015, 03:52 AM
Hi Larry

What an exotic life you seem to lead. Probably sunnier there too than Cornwall on a winter's day!!
Back to the more mundane....

"Sell"
Our previous checks suggest this is triggered correctly but (for the sake of your sanity and mine!) let's be absolutely certain
Jan 09 - cols J to M not triggered because "High" below \$7.89
Feb 11 - cols J to M triggered because "High" exceeds \$7.89, \$7.90, \$7.91,... \$7.94
Feb 11 - write margin values to Cols J to M based on the number of shares bought on Jan 09 (col G on Jan 09)
Fundamental question - is this correct?

"Buy"
Our macro leaves all values as per your formulas unless we tell it otherwise.
We have not told it not to buy - so the values in cols H to M are either your formulas or the result of the "sell" process above
So the next step is to tell the macro when to buy or when not to buy

What I need from you
I do not want to know what to put in any cell, I need to understand the process. Please try not to refer to any cells in your explanation.
After Jan 09 what triggers the next buy?
On Jan 10, if there is not a "buy" why not (for example - is it because we have not yet been able to sell Jan 09 purchases with margin 0.03 or because we have not sold Jan 09 purchases with margin 0.07, or some other condition)
On Feb 11 (if this is the next "buy") what is the trigger - what is the condition that must be met?

thanks
Yon

garretttpe
03-02-2015, 05:34 AM
Ok, We will look at the .02 cent column only for now
1. Jan 12 was a buy because we bought and sold on Jan 9th at .02 cents (Jan 10th and 11th are weekend days)
2. Jan 12 is a buy but no sell because the open to high relationship is only .01 which is less than 2 cents.
3. next sell will be on Feb 11 as that is the next time that the days open, high, low and close range exceeds Jan 12 buy price +.02
4. all cells from Jan 12 to Feb 10 will have no value or zero as there was no buy or sell days.
5. next buy will be Feb 13

Larry
I am from Northern Maine so I know a little about snow and extreme cold

Yongle
03-02-2015, 08:31 AM
Hi Larry

Sorry been so long, felt the need to go for a lie down to prepare for our next instalment (only joking - long lunch today)
I think the light may be beginning to shine here and, if that is so, it will have been worth the long wait.

Is this it?
- columns H to M are all independent of each other
- wthin each column a "buy" cannot happen until after the last "sell"
- a "sell" will not happen in any column unless the condition at the top of that column is satisfied (0.02 -> 0.07)
- when a "sell" occurs in any column, the value that is entered against that "sell" date is : the margin (top of that column) X the Number of shares purchased (column G on date of previous "buy")

If that is correct, then what triggers the next "buy" and when?
Is the "buy" automatic?
- if so does it happen the same day as previous "sell" or the next available trading day after that?
If the "buy" is not automatic, then a condition must trigger it
- what is that condition?
- which is the first day it can happen? - day of previous "sell" (in that column) or day following previous "sell" (in that column)

Non-trading days
Can you confirm that Column A excludes all non-trading dates

Yon
Having seen footage of weather conditions in Maine a few days ago, it looks like it was a wise move of yours to not be there this winter!!

garretttpe
03-02-2015, 11:28 AM
Is this it-----all correct

yes buy is automatic after a sell day. condition is on the OPEN price. Purchase price on OPEN the next trading day after a sell

Confirm that excludes all non trading days.

Tell my wife that she is there dealing with the snow, 3+ feet on the ground.

I wont be back on the computer until tomorrow. I am very glad we are getting extremely close to the same page.

Larry

Yongle
03-03-2015, 06:17 AM
Hi Larry

Here is your amended code. Hopefully the end numbers will please a little more this time. If there is any of the code that you cannot follow, I am happy to explain. I have included a few notes in the body of the code.
I have also attached a copy of the workbook, if you prefer just to run it there.
ctrl + shift + v clears sheet1 and replaces with clean values
ctrl + shift + s runs the macro below

Yon
Your wife deserves a nice piece of gold for her sterling work with the snow shovel, or perhaps you could just buy her a new shovel in case she wore out the old one!!

Sub Next_Sell_And_Next_Buy()
12952
'Clear out previous values
Sheets("Sheet1").Range("A1:M255").ClearContents
Sheets("Rite Aid Data").Range("A1:M255").Copy
Sheets("Sheet1").Range("A1").Select
ActiveSheet.Paste
'set up variables

Dim Shares As String, Margin As String, CellRef As String
Dim r As Integer, c As Integer
Dim MinSellPrice As Single, Price As Single

'test each cell
For c = 8 To 13 ' columns H to M
For r = 4 To 255 'rows 4 to 255

If Cells(r, 2).Value > 0 Then 'only do this if there is a price in Column B

Price = Cells(r, 2).Value
MinSellPrice = Price + Cells(3, c).Value
If Cells(r, c) = 0 Then
For r2 = r + 1 To 255
If Cells(r2, 3) >= MinSellPrice Then
Shares = Cells(r, 7).Address
Margin = Cells(3, c).Address

Cells(r2, c).Select
CellRef = "=" & Shares & "*" & Margin
ActiveCell.Formula = CellRef ' "sell" formula
'the next "buy" will happen automatically (per formula)
r = r2 'reset the controlling row counter to equal current row
r2 = 255 'stock is sold so test all "sell" conditions afresh
Else
'if no sale remove value from cell
Cells(r2, c).Value = 0
End If
Next r2
Else
'do nothing
End If
Else: r = 255
End If
Next r
Next c
End Sub

Yongle
03-03-2015, 06:20 AM
The attachment to previous post is now attached to this one

garretttpe
03-03-2015, 07:16 AM
thank you so much, it look like it is working awesome. I am on a very tight schedule now as I am going back to the states shortly and I am extremely busy. I cannot thank you enough for working this issue with me. Over the next couple of weeks I will be working this heavy as my time permits and I will be keeping you informed. again a world of thanks.

Laryr