Getting no results for WorksheetFunction.Large

[TheRedCardinal]

Hi all,

I need to find the highest x values in a range on a spreadsheet. They are contained in a range in column F of a sheet called "Temp", which has been assigned variable WS4.

My code looks like this:

For Each CellA In Range(WS4.Cells(1, 6), WS4.Cells(b, 6))

    If CellA.Value = WorksheetFunction.Large(WS4.Range(WS4.Cells(1, 6), WS4.Cells(b, 6)), WS3.Range("VATSalesCheck")) Then
    
        z = z + 1
        y = CellA.Row
        
        For Counter = 1 To 6
        
            VatSalesCheck(z, Counter) = VATSales(y, Counter)
        
        Next Counter
        
    End If

Next CellA

The purpose is to load the details into the Array VATSalesCheck of whatever value is in the cell called "VATSalesCheck" on WS3. (Currently 5)

But in the above IF statement, I never get a TRUE result, even though when stepping through, the CellA.Value popup value matches the ones on the sheet that have been highlighted by the conditional formatting of top 5 values.

Can anybody see what I've done wrong?

 

[Rick Rothstein]

Just guessing based on other parts of your code, but should your Range object in the For Each statement have WS4. preceding it?

For Each CellA In WS4.Range(WS4.Cells(1, 6), WS4.Cells(b, 6))

 

[TheRedCardinal]

Just guessing based on other parts of your code, but should your Range object in the For Each statement have WS4. preceding it?

For Each CellA In WS4.Range(WS4.Cells(1, 6), WS4.Cells(b, 6))

I'll give it a go but I don't think so, because it successfully loops through all of the cells and I can match each CellA value to the corresponding cell value on the worksheet when I step through the macro.

 

[Alex Blakenburg]

You seem to have a lot of variables that you are not showing us.
Also I don't think its a great idea to have an array variable VATSalesCheck with the same name as your single cell named range VATSalesCheck.

Put this before your If Statement and see if anything it shows doesn't make sense.

        MsgBox "If Statement: " & vbTab & vbTab & (CellA.Value = WorksheetFunction.Large(WS4.Range(WS4.Cells(1, 6), WS4.Cells(b, 6)), WS3.Range("VATSalesCheck"))) & _
                    Chr(10) & _
                    "Large Return Value: " & vbTab & _
                        WorksheetFunction.Large(WS4.Range(WS4.Cells(1, 6), WS4.Cells(b, 6)), WS3.Range("VATSalesCheck")) & Chr(10) & _
                    "Value of b: " & vbTab & vbTab & b & Chr(10) & _
                    "Large Range:" & vbTab & vbTab & WS4.Range(WS4.Cells(1, 6), WS4.Cells(b, 6)).Address & Chr(10) & _
                    "VatSalesCheck Value: " & vbTab & WS3.Range("VATSalesCheck") & Chr(10) & _
                    "CellA address: " & vbTab & vbTab & CellA.Address & Chr(10) & _
                    "CallA Value: " & vbTab & vbTab & CellA.Value

 

[TheRedCardinal]

You seem to have a lot of variables that you are not showing us.
Also I don't think its a great idea to have an array variable VATSalesCheck with the same name as your single cell named range VATSalesCheck.

Put this before your If Statement and see if anything it shows doesn't make sense.

        MsgBox "If Statement: " & vbTab & vbTab & (CellA.Value = WorksheetFunction.Large(WS4.Range(WS4.Cells(1, 6), WS4.Cells(b, 6)), WS3.Range("VATSalesCheck"))) & _
                    Chr(10) & _
                    "Large Return Value: " & vbTab & _
                        WorksheetFunction.Large(WS4.Range(WS4.Cells(1, 6), WS4.Cells(b, 6)), WS3.Range("VATSalesCheck")) & Chr(10) & _
                    "Value of b: " & vbTab & vbTab & b & Chr(10) & _
                    "Large Range:" & vbTab & vbTab & WS4.Range(WS4.Cells(1, 6), WS4.Cells(b, 6)).Address & Chr(10) & _
                    "VatSalesCheck Value: " & vbTab & WS3.Range("VATSalesCheck") & Chr(10) & _
                    "CellA address: " & vbTab & vbTab & CellA.Address & Chr(10) & _
                    "CallA Value: " & vbTab & vbTab & CellA.Value

Hi thanks for trying to help.

Firstly, yes I know there are variables I haven't explained but they "work" and I didn't try to explain them because everything was working fine variable wise except that the IF statement always returned negative.

Also yes I agree that having the same name used twice is bad technique and I'll change them.

That said, I put your code in (excellent idea by the way!) and it returned exactly as I expected.

Here is one extract from the third loop of the For/Next routine:

This Cell is highlighted on the sheet as having one of the top 5 values.

All of the ranges check out.

However I realised that it is not in fact returning False in every line. I was intrigued by that "Large Return Value" because it actually worked for that line (it's row 122 of 138) and that line only - and as you can see it's the fifth highest number, so the ones it skipped were higher still.

This twist has absolutely thrown me now.

 

[TheRedCardinal]

Have I solved my own problem here by misunderstanding what the Large function does?

The second argument specifies the position in the sorted range, i.e. in my example the number 5 returns the 5th highest value, not the 5 highest values...?

 

[TheRedCardinal]

Thanks everybody, I got there in the end.
New code:

For Counter1 = 1 To WS3.Range("VATSalesCheck")

    For Each CellA In Range(WS4.Cells(1, 6), WS4.Cells(b, 6))
                    
        If CellA.Value = WorksheetFunction.Large(WS4.Range(WS4.Cells(1, 6), WS4.Cells(b, 6)), Counter1) Then
    
            y = CellA.Row
        
            For Counter = 1 To 6
        
                VatSalesCheck(Counter1, Counter) = VATSales(y, Counter)
        
            Next Counter
        
        End If

    Next CellA

Next Counter1

Works like a dream!

Sorry for wasting everybody's time

 

[MARK858]

Maybe something like the code below (doesn't take any account of duplicate values) which only uses the one loop.

Sub TheRedCardinal()
    Dim myRng As Range, i As Long, j As Long
    Dim VATSalesCheck As String, b As Long, ws4 As Worksheet
  
    Set ws4 = Sheets("Temp")
    b = ws4.Cells(Rows.Count, 6).End(xlUp).Row
    Set myRng = Range(ws4.Cells(1, 6), ws4.Cells(b, 6))

    j = 0
    For i = 1 To 5
        j = Application.WorksheetFunction.Large(myRng, i)
        VATSalesCheck = VATSalesCheck & "Rank " & i & " is " & j & vbCr
    Next i

    MsgBox VATSalesCheck

End Sub

or one way of putting the result in the worksheet

Sub TheRedCardinal2()
    Dim myRng As Range, i As Long, j As Long
    Dim VATSalesCheck(1 To 5) As Long, b As Long, ws4 As Worksheet
   
    Set ws4 = Sheets("Temp")
    b = ws4.Cells(Rows.Count, 6).End(xlUp).Row
    Set myRng = Range(ws4.Cells(1, 6), ws4.Cells(b, 6))

    j = 0
    For i = 1 To 5
        j = Application.WorksheetFunction.Large(myRng, i)
        VATSalesCheck(i) = j
    Next i

    Range("A2").Resize(i - 1) = Application.Transpose(VATSalesCheck)

End Sub