+ Reply to Thread
Results 1 to 3 of 3

FIND and MINIMUM function problem

  1. #1
    Forum Contributor
    Join Date
    03-03-2005
    Posts
    315

    FIND and MINIMUM function problem

    I have had problems with Max and Min functions in their application to arrays but nothing like this. The following line code strangely fails to identify the minimum value in a range (but corrrectly isolates the Maximum when Max is substituted for Min):

    Sub FindMin()
    set rng = Range("a1:d20")
    Range("a:d").Find(WorksheetFunction.Min(rng)).select
    End Sub

    The above assumes that [a1:d20] is populated with values. For ... each loop can do the job but I prefer in this case the brevity of Find method.


    Any ideas about the failure?

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello David,

    Over the years when VBA exhibits eccentric behavior, I have found that using qualifiers generally will restabilize the code, but not always. Every version of Office and Windows has it's share of undocumented "features". Try the code as written here and let me know what happens.

    Sub FindMin()
    Dim rng As Excel.Range
    set rng = Range("a1:d20")
    Range("a:d").Find(Excel.WorksheetFunction.Min(rng)).select
    End Sub


    Hope this helps,
    Leith Ross

  3. #3
    Forum Contributor
    Join Date
    03-03-2005
    Posts
    315
    Leith,

    Tried on a local scale, your adjustment works but, so does the original. I am providing the full code which contains the Find line of code. As previously noted, The max value is corrected picked all the time, the Min hapzardly picked-wrongly!

    Sub UniqueRandnosInMulitRange1()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    [a:d].Clear

    Set rng = Range("a1:d25")
    For Each c In rng
    Do
    Randomize
    x = Int(Rnd * 100 + 1)
    c.Value = x
    Loop While Application.CountIf(rng, c.Value) = 2
    Next

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Range("a:d").Find(.WorksheetFunction.Max(rng)).Interior.Color = vbYellow
    Range("a:d").Find(WorksheetFunction.Min(rng)).Interior.Color = vbYellow

    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1