PDA

View Full Version : Help on some VBA



alwayslearn
09-04-2008, 04:42 PM
I have a set of data in chart form which is generated by a button on my worksheet.

I have another button and I need help getting started on the code for it.

The other button (button 2) needs VBA code so that when clicked it populates another chart below and the other two sets(all the red boxes) the first chart with math calculations.

Can someone take a look at the attached spreadsheet and get me started or help me out?

mikerickson
09-04-2008, 10:46 PM
Are these the calulations you want?
Dim theRange As Range
With Sheets("sheet1")
Set theRange = .Range("b2:K2")
With .Range("b18:B27")
.FormulaR1C1 = "=AVERAGE(" & theRange.Address(False, False, xlR1C1, True, .Cells(1, 1)) & ")"
.Offset(0, 1).FormulaR1C1 = "=MEDIAN(" & theRange.Address(False, False, xlR1C1, True, .Cells(1, 2)) & ")"
.Offset(0, 2).FormulaR1C1 = "=MODE(" & theRange.Address(False, False, xlR1C1, True, .Cells(1, 3)) & ")"
.Offset(0, 3).FormulaR1C1 = "=R[-16]C2"
.Offset(0, 4).FormulaR1C1 = "=MAX(" & theRange.Address(False, False, xlR1C1, True, .Cells(1, 4)) & ")"
.Offset(0, 5).FormulaR1C1 = "=MIN(" & theRange.Address(False, False, xlR1C1, True, .Cells(1, 5)) & ")"
.Offset(0, 6).FormulaR1C1 = "=R[-16]C11"
.Offset(0, 7).FormulaR1C1 = "=RC[-3]-RC[-2]"
End With
End With

Bob Phillips
09-05-2008, 02:07 AM
I think that there is a small typo in Mike's code



Dim theRange As Range
With Sheets("sheet1")
Set theRange = .Range("b2:K2")
With .Range("b18:B27")
.FormulaR1C1 = "=AVERAGE(" & theRange.Address(False, False, xlR1C1, True, .Cells(1, 1)) & ")"
.Offset(0, 1).FormulaR1C1 = "=MEDIAN(" & theRange.Address(False, False, xlR1C1, True, .Cells(1, 2)) & ")"
.Offset(0, 2).FormulaR1C1 = "=MODE(" & theRange.Address(False, False, xlR1C1, True, .Cells(1, 3)) & ")"
.Offset(0, 3).FormulaR1C1 = "=R[-16]C2"
.Offset(0, 4).FormulaR1C1 = "=MAX(" & theRange.Address(False, False, xlR1C1, True, .Cells(1, 5)) & ")"
.Offset(0, 5).FormulaR1C1 = "=MIN(" & theRange.Address(False, False, xlR1C1, True, .Cells(1, 6)) & ")"
.Offset(0, 6).FormulaR1C1 = "=R[-16]C11"
.Offset(0, 7).FormulaR1C1 = "=RC[-3]-RC[-2]"
End With
End With

alwayslearn
09-05-2008, 06:11 AM
Thanks --you guys are great--this really helps--how about the code for those last 4--I especially am having trouble with the second highest--does excel all for this?



Number with Highest Close Value


Number with Second Highest Close Value






Day with Lowest Close Value


Day with Second Lowest Close Value

Bob Phillips
09-05-2008, 06:31 AM
Here is some code, but ...

... isn't the day with the lowest close value always day 10, that is where Mike gets it from



Dim theRange As Range
With Sheets("sheet1")
Set theRange = .Range("b2:K2")
With .Range("b18:B27")
.FormulaR1C1 = "=AVERAGE(" & theRange.Address(False, False, xlR1C1, True, .Cells(1, 1)) & ")"
.Offset(0, 1).FormulaR1C1 = "=MEDIAN(" & theRange.Address(False, False, xlR1C1, True, .Cells(1, 2)) & ")"
.Offset(0, 2).FormulaR1C1 = "=MODE(" & theRange.Address(False, False, xlR1C1, True, .Cells(1, 3)) & ")"
.Offset(0, 3).FormulaR1C1 = "=R[-16]C2"
.Offset(0, 4).FormulaR1C1 = "=MAX(" & theRange.Address(False, False, xlR1C1, True, .Cells(1, 5)) & ")"
.Offset(0, 5).FormulaR1C1 = "=MIN(" & theRange.Address(False, False, xlR1C1, True, .Cells(1, 6)) & ")"
.Offset(0, 6).FormulaR1C1 = "=R[-16]C11"
.Offset(0, 7).FormulaR1C1 = "=RC[-3]-RC[-2]"
End With
.Range("P15").Formula = "=LARGE(H18:H27,1)"
.Range("P16").Formula = "=LARGE(H18:H27,2)"
.Range("P18").FormulaArray = "=INDEX(B1:K1,MATCH(SMALL(H18:H27,1),INDEX(B2:K11,MATCH(SMALL(H18:H27,1),H18 :H27,0),0),0))"
.Range("P19").FormulaArray = "=INDEX(B1:K1,MATCH(SMALL(H18:H27,2),INDEX(B2:K11,MATCH(SMALL(H18:H27,2),H18 :H27,0),0),0))"
End With

alwayslearn
09-05-2008, 06:37 AM
No the day with the lowest closing value could be anyday not just Day 10--also the two large formulas produce the number itself--I need which value it would be in the range of A18 to A27.

alwayslearn
09-05-2008, 07:10 AM
Number was expressed in a bad way.

The "Number" with the highest close value would be a max formula on h18 to h27 but it would not give the number value but rather the "number" in A18 to A27. The "number" could represent a school or a person, etc.

I attached a file and put the number I need returned in green.

Bob Phillips
09-05-2008, 07:22 AM
That explained zilch to me, what should be the value in P15:P16, P18:P19 based upon your original post, and why?

alwayslearn
09-05-2008, 07:37 AM
Sorry about that--

The values in P15:P16 should be one of the values in A18:A27(Number 1, Number 2, Number 3...etc)

The values in P18:P19 should be one of the values in B1:K1(Day 1, Day 2, Day 3...etc)

They values should be the actual word (number 1, number 2--Day 1, Day 2)

Bob Phillips
09-05-2008, 07:44 AM
I know that, I wanted you to work out which so that I could compare actual resullts with predicted results.

My previous comments re day of lowest closing value still stand



Dim theRange As Range
With Sheets("sheet1")
Set theRange = .Range("b2:K2")
With .Range("b18:B27")
.FormulaR1C1 = "=AVERAGE(" & theRange.Address(False, False, xlR1C1, True, .Cells(1, 1)) & ")"
.Offset(0, 1).FormulaR1C1 = "=MEDIAN(" & theRange.Address(False, False, xlR1C1, True, .Cells(1, 2)) & ")"
.Offset(0, 2).FormulaR1C1 = "=MODE(" & theRange.Address(False, False, xlR1C1, True, .Cells(1, 3)) & ")"
.Offset(0, 3).FormulaR1C1 = "=R[-16]C2"
.Offset(0, 4).FormulaR1C1 = "=MAX(" & theRange.Address(False, False, xlR1C1, True, .Cells(1, 5)) & ")"
.Offset(0, 5).FormulaR1C1 = "=MIN(" & theRange.Address(False, False, xlR1C1, True, .Cells(1, 6)) & ")"
.Offset(0, 6).FormulaR1C1 = "=R[-16]C11"
.Offset(0, 7).FormulaR1C1 = "=RC[-3]-RC[-2]"
End With
.Range("P15").Formula = "=INDEX(A18:A27,MATCH(LARGE(H18:H27,1),H18:H27,0))"
.Range("P16").Formula = "=INDEX(A18:A27,MATCH(LARGE(H18:H27,2),H18:H27,0))"
.Range("P18").FormulaArray = "=INDEX(B1:K1,MATCH(SMALL(H18:H27,1),INDEX(B2:K11,MATCH(SMALL(H18:H27,1),H18 :H27,0),0),0))"
.Range("P19").FormulaArray = "=INDEX(B1:K1,MATCH(SMALL(H18:H27,2),INDEX(B2:K11,MATCH(SMALL(H18:H27,2),H18 :H27,0),0),0))"
End With

alwayslearn
09-05-2008, 08:01 AM
Thanks. I am getting the correct results for the highest and 2nd highest values.

As for the lowest and 2nd lowest data:

If you use the generate matrix command button sometimes day 10 does not have the lowest day value.

This the most difficult formula...so..

It would be taking the lowest and 2nd lowest figure in H18:27 then finding out which day this figures falls under in the first chart then return those days.

Bob Phillips
09-05-2008, 08:07 AM
Maybe it doesn't, but your instructions are the day of lowest close, and the code MR provided gets the close value from K3, K4, etc. which is ALWAYS Day 10.

alwayslearn
09-05-2008, 08:17 AM
Thanks--let me look into it further and see If I can fix it. Your the man XLD--let me know if you need anything.