-
Feb 23rd, 2012, 09:52 AM
#1
Thread Starter
New Member
[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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|