Excel 2003 - VBA - Mixing Static and Dynamic Functions - OUCH!!

[Craig Brandt]

I have a table of selected stocks with associated data. I would like to
highlight, with background color, the parameters that are the best in class
(Green) as well as those that are worst in class (Yellow).
To accomplish this I cycle through the columns and sort the table by the
parameter, then simply number a copy of the chart layout in assending
numbers, then go to the next parameter. When this is done I should have two
identical tables, one wuth the values and the other with that values
respective position. I then use conditionals in the value table to look at
the position and color code those parameters that have a ranking of 1-10,
green and the last 10 in yellow.
Value table in AT3:BA106 [Range(Cells(3,46),Cells(MaxSymbols,53))]
Ranking table BB3:BI106

MaxSymbols = 106
Public Sub RankBySTDEV()
Sheets("Param").Select
Range(Cells(3, 46), Cells(MaxSymbols, 61)).Select
Selection.Sort Key1:=Cells(3, 48), Order1:=xlAscending
Cells(3, 56).FormulaR1C1 = "1"
Cells(4, 56).FormulaR1C1 = "2"
Range(Cells(3, 56), Cells(4, 56)).AutoFill Destination:=Range(Cells(3,
56), Cells(MaxSymbols, 56)), Type:=xlFillDefault
End Sub

Problem: The procedure doesn't seem to wait for the sort to complete before
it assigns the "static" ranking values.

Any ideas?

Craig

 

[Bernie Deitrick]

Craig,

Don't sort. Use a formula rather than sorting and applying fixed numbers: This also set MaxSymbols
to the filled rows based on column 48 (Column AV).


Public Sub RankBySTDEV2()

With Sheets("Param")
MaxSymbols = .Cells(Rows.Count, 48).End(xlUp).Row
.Range(.Cells(3, 56), .Cells(MaxSymbols, 56)).Formula = _
"=RANK(" & .Cells(3, 48).Address(False, False) & "," & _
.Range(.Cells(3, 48), .Cells(MaxSymbols, 48)).Address & ",1)"
' use 0 instead of 1 for other ranking order
' 'Then convert to values - or comment out to leave as formulas...
.Range(.Cells(3, 56), .Cells(MaxSymbols, 56)).Value = _
.Range(.Cells(3, 56), .Cells(MaxSymbols, 56)).Value
End With

End Sub


HTH,
Bernie
MS Excel MVP

 

[Shane Devenshire]

Hi,

Just a comment you can use conditional formatting to format the cells.

 

[Craig Brandt]

Bernie:

Thanks for the hint on RANK. That solved that instance of the problem.
Isn't there a command that says "wait until all calculations are complete
before proceeding". I keep running into this problem of acting on data that
isn't ready. It is especially obvious when you do query's, then try and use
the data. The only solution that we came up with there, was to do the
query's with one buttom and the procedure that uses the data with another
and simply wait until it looks like all the activity with the query is
complete before depressing the other button.

Again, thanks for the solution. I truly apprieciate the help that I get on
this site.

Craig

Craig,

Don't sort. Use a formula rather than sorting and applying fixed

numbers: This also set MaxSymbols

to the filled rows based on column 48 (Column AV).


Public Sub RankBySTDEV2()

With Sheets("Param")
MaxSymbols = .Cells(Rows.Count, 48).End(xlUp).Row
.Range(.Cells(3, 56), .Cells(MaxSymbols, 56)).Formula = _
"=RANK(" & .Cells(3, 48).Address(False, False) & "," & _
.Range(.Cells(3, 48), .Cells(MaxSymbols, 48)).Address & ",1)"
' use 0 instead of 1 for other ranking order
' 'Then convert to values - or comment out to leave as formulas...
.Range(.Cells(3, 56), .Cells(MaxSymbols, 56)).Value = _
.Range(.Cells(3, 56), .Cells(MaxSymbols, 56)).Value
End With

End Sub


HTH,
Bernie
MS Excel MVP

I have a table of selected stocks with associated data. I would like to
highlight, with background color, the parameters that are the best in class
(Green) as well as those that are worst in class (Yellow).
To accomplish this I cycle through the columns and sort the table by the
parameter, then simply number a copy of the chart layout in assending
numbers, then go to the next parameter. When this is done I should have two
identical tables, one wuth the values and the other with that values
respective position. I then use conditionals in the value table to look at
the position and color code those parameters that have a ranking of 1-10,
green and the last 10 in yellow.
Value table in AT3:BA106 [Range(Cells(3,46),Cells(MaxSymbols,53))]
Ranking table BB3:BI106

MaxSymbols = 106
Public Sub RankBySTDEV()
Sheets("Param").Select
Range(Cells(3, 46), Cells(MaxSymbols, 61)).Select
Selection.Sort Key1:=Cells(3, 48), Order1:=xlAscending
Cells(3, 56).FormulaR1C1 = "1"
Cells(4, 56).FormulaR1C1 = "2"
Range(Cells(3, 56), Cells(4, 56)).AutoFill Destination:=Range(Cells(3,
56), Cells(MaxSymbols, 56)), Type:=xlFillDefault
End Sub

Problem: The procedure doesn't seem to wait for the sort to complete before
it assigns the "static" ranking values.

Any ideas?

Craig