Results 1 to 5 of 5

Thread: [RESOLVED] VBA Excel: Problem with WorksheetFunction.Large?

Threaded View

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    6

    Resolved [RESOLVED] VBA Excel: Problem with WorksheetFunction.Large?

    Hey guys,

    I'm having a problem of whgich I have no idea how it came into being. It's about this bit of code:

    Code:
    Sub GrootsteWaarden()
    
        Row = 3 'Begin bij rij 3
        RowEnd = 14 'Eindig bij rij 14
        
        MsgBox Top1
    
        Top1 = WorksheetFunction.Large(Range("F" & Row & ":" & "F" & RowEnd), 1)
        
        MsgBox Top1
        
        WaardenRegel1 = WorksheetFunction.Match(Top1, Range("F" & Row & ":" & "F" & RowEnd), 0) + 2
        Range("B" & WaardenRegel1 & ":" & "F" & WaardenRegel1).Interior.Color = 65535
        
        Top2 = WorksheetFunction.Large(Range("F" & Row & ":" & "F" & RowEnd), 2)
        WaardenRegel2 = WorksheetFunction.Match(Top2, Range("F" & Row & ":" & "F" & RowEnd), 0) + 2
        Range("B" & WaardenRegel2 & ":" & "F" & WaardenRegel2).Interior.Color = 65535
        
        Top3 = WorksheetFunction.Large(Range("F" & Row & ":" & "F" & RowEnd), 3)
        WaardenRegel3 = WorksheetFunction.Match(Top3, Range("F" & Row & ":" & "F" & RowEnd), 0) + 2
        Range("B" & WaardenRegel3 & ":" & "F" & WaardenRegel3).Interior.Color = 65535
    
    End Sub
    What the code doe,s is find the three highest values in a range, then get their row numbers, and finally make the corresponding row yellow. The valuyes themselves and their row numbers are stored for later use.

    Note that I didn't include any of the variable definitons, but I all defined them outside of any sub, as public variables so that all subs can use them if necessary.

    Now, the problem is that the WorksheetFunction.Large line is suddenly ALWAYS returning "1" instead of the highest number, which would be 0.65 or something, and I have no idea why. It worked beautifully an hour ago...

    It seems the problems started when I copied the sub to another module, but deleting it form that module doesn't remedy the situation. I have checked the code time on time again, and see absolutely no reason for to happen.

    I have no idea whatsoever what to do.I hope anybody can help...

    PS: there are some thing in there that are there purely for my debugging purposes, like the message boxes. When all starts working again, they will be removed.
    Last edited by Thralni; Feb 28th, 2012 at 03:01 AM. Reason: Problem resolved

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width