VBA Rank error 1

[RicksAtWork]

I have the following code which is suposed to determine the rank of the cells in defined range:

Private Sub ApplyRankings(targetWorkSheet As Worksheet)

Dim grandTotalRange As Range
Dim grandTotalRangeString As String
'Dim targetCell As CellFormat


Dim lastRow As Integer
lastRow = lngNextRow(targetWorkSheet.Name, 1) - 1

grandTotalRangeString = "U2:U" + CStr(lastRow)
Set grandTotalRange = Range(grandTotalRangeString)

For i = 2 To lastRow

#ERROR HERE
targetWorkSheet.Cells(i, U).Value = Application.WorksheetFunction.Rank(targetWorkSheet.Cells(i, U), grandTotalRange)

Next i



End Sub

I have Highlighted the position the error occurs with #ERROR HERE.

It is an object-defined error.

I think the problem is a result of the manner in which I am selecting the cell.

How should I approach this?

 

[RicksAtWork]

The error is actually:

Unable to get the Rank property of the WorkSheetFunctionClass

Ideas?

 

[GlennUK]

Do you think this might be caused by the fact that you are trying to rank a cell that you're putting the answer in to?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[PHV]

Replace this:
.Cells(i, U).
with this:
.Cells(i, "U").

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GlennUK]

That'll teach not to read the rest of the code .... assumed that U was a variable.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[RicksAtWork]

Actually GlennUK was 100% correct - the other error was a typo!

Good work Glenn!!

 

[GlennUK]

Blimey, I was right!!! Thanks for the star.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.