Dear all,
I'm a new user of Visual Basic (version 6.3) and need to write a Macro for use in Excel. I need the Macro to calculate Rank values for an unpredictable number of scores.
The number of scores that need to be Ranked will vary and I don't know how to incorporate this variation into the Visual Basic Macro.
In my current spreadsheet there are 11 scores (Sorted in descending order) in column DS (cells DS2:DS12). The Visual Basic code is as follows (DT is the column where the Rank values will be sorted):
Range("DT2").Select
ActiveCell.FormulaR1C1 = "=RANK(RC[-1],C[-1],1)"
Selection.AutoFill Destination:=Range("DT2:DT12"), Type:=xlFillDefault
Range("DT2").Select
You can see that the current Range is DT2:DT12 (i.e. 11 scores). This code works fine for Ranking 11 scores, but if I get 89 scores how can I change the code beforehand to (a) recognise there are 89 scores; and (b) provide a Ranking for all 89 (The problem is that I need to prepare the Macro upfront before I know how many responses there will be - I'll be giving the spreadsheet to someone else and it'll all need to run automatically).
I've tried using the DCount function in Excel to count the number of scores in the DS column and then incorporating the DCount value into the code above (with no luck!) I'm a complete novice and I'm hoping someone will be able to help.
If I've been unclear in any way, please email me at: jamesmeachin[at]hotmail[dot]com
Best regards,
James
Defining a range according to the number of responses
Jeff Sessions
Hi Derek,
You're a star! Thank you very much for your help - most kind.
Best regards,
James
Tony La Sala
Nickthegreek
James,
There are a few ways you can do this.
You could have a cell where the user enters the number of scores and you use this number to specify the range similar to what your doing now. Range("DT2:DT" & Cell.Value) sort of idea.
Another way would be to allow the user to select the cells that contain the scores in which case you could use the Range.Selection to get the cells selected. Do your calculation using that. (check selection in help)
Another way would be to loop down the column checking each value in the cell. Once you find say 10 blank cells in a row then you know your range.
Or, and I think this might be the one to check out first, you can use the Range objects End property to find the last cell of a region. Look up Range in the help and look for the End property. It gives good example. This searches down from A2 to find the last row that contains data.
nEndRowIndex = ActiveSheet.Range("A2").End(xlDown).Row
Hope that helps
Mike Weerasinghe
Hey James,
Do not appologise for being slow it could just be I've not understood the problem.
The way I'm picturing it is you have a column filled with scores, and there will be a varied number of these scores. Your code currently selects cell DT2, sets this cells formula to RANK(), and then you autofull the rows below it with the formula for, I'm assuming, the number of scores entered.
Selection.AutoFill Destination:=Range("DT2:DT12"), Type:=xlFillDefault
That's the line your looking to change in your code and you want to change the range based on the number of scores entered.
nScoreCount = ActiveSheet.Range("DT2").End(xlDown).Row 'Where cell DT2 contains the first score, and there are no empty rows within the list of scores
Selection.AutoFill Destination:=Range("DT2:DT" & nScoreCount), Type:=xlFillDefault
Try that.
mattaz
Hi Derek,
Thanks for replying so quickly. The Range objects End property makes sense, although I'm not sure how I can incorporate the result into my code:
Range("DT2").Select
ActiveCell.FormulaR1C1 = "=RANK(RC[-1],C[-1],1)"
Selection.AutoFill Destination:=Range("DT2:DT12"), Type:=xlFillDefault
Range("DT2").Select
At present, the Macro calculates a Range value in cell DT2 based on the score in cell DS2 in comparison to all other active cells in the DS column. It then autofills to the (at present pre-defined) rows DT2:DT12.
As I understand it, lines 1 & 2 of the code will work irrespective of the number of scores in column DS. However, I'm not sure how to use the result of the Range objects End property to influence the AutoFill command.
Apologies if I'm being slow, any advice would be very gratefully recieved!
Best regards,
James