How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask The-PC-Guy Your Own Question
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1978
Experience:  20 years experience providing remote computer support
Type Your Microsoft Office Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

OK this question is back to the MAC situation and the Top

Customer Question

Hi Andrew, OK this question is back to the MAC situation and the Top Ten Macro Code you wrote for me which works GREAT on the PC. I tried the file you sent where you commented out the four lines of code that dealt with 'application' as you said that was
strictly PC code - you also said you deleted a few other lines of code but it still does not work. I know you said there were no guarantees that it will work on a MAC but I figured there has to be a way to accomplish a Top Ten list for the MAC somehow? All
the other macro codes work on the MAC except the Top Ten. If you cannot figure out a way to get it to work do you know of another programmer within Just Answer who may be able to help me? All 5 of the Top Ten macros have basically the same error message which
is: Run-time error '9': Subscript out of range When you press Debug it highlights the line of code that states: If UBound (f) < 0 Then The only macro code that stops at a different point within the code is for the Top Ten Mile - same line is highlighted just
in a different location as shown below: For Each rcell In wks.Range("I2:I" & lr) f = Filter(arrayvals2, rcell.Value) If UBound(f) < 0 Then wks.Rows(rcell.Row).ClearContents End If Next rcell All the other Top Ten codes stop at the following section of code:
For i = 2 To 11 cval = wks.Range("I" & i).Value f = Filter(arrayvals, cval) If UBound(f) < 0 Then ReDim Preserve arrayvals(A) arrayvals(A) = cval A = A + 1 End If Next i Obviously if you can help me fix this code so it works on the MAC there would be a nice
tip! Thanks for anything you can do. Steve
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  The-PC-Guy replied 1 year ago.

subscript range means the array is not being populated for whatever reason . you can try supressing all errors by adding

On Error Resume Next

at the begining of the script, this of course may cause the macro to not function all together , but it should not throw and error.

let me know what this does, it may cause it to not populate althogether or wierd vvalues to occur, let me know if this happens and I can try looking in the code deaper to see if i can cause the array to not populate

Customer: replied 1 year ago.

OK I placed your line of code at the start of each macro - turned off automatic calculation and it worked without error -

For each macro it said there should be 99 Top Scores which was obviously wrong but what it did show was the VERY FIRST RESULT ONLY. I checked and it did return the very best result but only one results where there should have been 11 for the mile etc.

So you are correct it is not populating the entire list of Top Ten results - it comes up with the first one and then it must crash.

Unfortunately I have to return the MAC book that I borrowed and will not be able to get another one for about a week - so if you can think of a work around for this problem - GREAT but I will not be able to test it for a while.

Thanks for your help so far I think you are getting close!!!!!!

Expert:  The-PC-Guy replied 1 year ago.

i will look at it further , however as a time saver you might want to consider replacing the mac with a pc. or if you must use a mac, consider using paraleels to run the pc version of excel.

Related Microsoft Office Questions