PDA

View Full Version : Solved: VBA Code help.



djonjohn1252
05-24-2012, 10:54 PM
So I have an excel project I have been working on with two fellow employees, were basically learning how to modify some existing code. The problem is we have gotten stuck on a few items and was hoping someone could show us how its done, I have attached the code and the data file for the macro's.

The last 4 issues we have to deal with are

1. Move the buttons on the worksheet over a couple of columns to make room for two more computed columns.

2. Change the program so that it leaves columns A- F empty when it loads the data.

3. In column D, compute the 6-day moving average of the 1-day average. Give this column an appropriate heading, too, and add a button to trigger this computation. Note you should be adding 6 things and dividing by 6.

4. In column E, have the program print “buy” when the close is over the 6-day moving average for the first day after being below it, and “sell” when the close is under the 6-day moving average for the first time after being over it. Give this column an appropriate heading, and add a button to trigger this computation.

Aussiebear
05-25-2012, 01:43 AM
For the first issue, turn the design mode on, right click on each button and then select the button by holding down the left button and drag to your preferred position.

Aussiebear
05-25-2012, 02:06 AM
In relation to the 3rd issue, if the compute 3 day average works, surely you are simply looking to change a couple of things from

Sub Compute3DayAvg()
Dim rowNdx As Integer

rowNdx = FIRSTDATAROW + 2
Do While Not IsEmpty(Cells(rowNdx, DATECOL).Value)
Cells(rowNdx, AVG3COL).Value = (Cells(rowNdx - 2, AVG1COL).Value _
+ Cells(rowNdx - 1, AVG1COL).Value _
+ Cells(rowNdx, AVG1COL).Value) / 3
rowNdx = rowNdx + 1
Loop
End Sub
to the following,

Sub Compute6DayAvg()
Dim rowNdx As Integer

rowNdx = FIRSTDATAROW + 5
Do While Not IsEmpty(Cells(rowNdx, DATECOL).Value)
Cells(rowNdx, AVG6COL).Value = (Cells(rowNdx - 5, AVG1COL).Value, + Cells(rowNdx - 4, AVG1Col).Value, + Cells(rowNdx -3, AVG1Col).Value, _
+ Cells(rowNdx -2, AVG1Col).Value, + Cells(rowNdx -1, AVG1Col).Value, +Cells(rowNdx, AVG1Col).Value, + Cells(rowNdx, AVG1COL).Value) / 6
rowNdx = rowNdx + 1
Loop
End Sub

Aussiebear
05-25-2012, 02:18 AM
In relation to the 2nd issue, try changing the following Dim'd statements from

Const DATECOL As Integer = 5

to


Const DATECOL As Integer = 7


and see how you go.

djonjohn1252
05-25-2012, 02:42 AM
That fixed the first, second and third issue perfectly.

djonjohn1252
05-25-2012, 01:25 PM
Anyone have a clue about the last issue, still stuck on it.