How to select a horse/dog if it is the favourite?

[cjones198]

Hi if anyone can help please?

I wish to write a piece of code that goes through column O and selects the favourite based on it being the lowest price or through column P based on it having the most amount of money matched.

and using it as the selection for the rest of the code to refer to (its for autorecording Australian markets and the dogs) which are not listed favourite first.

ive tried the following code

Code: Select all

Sub find_fav()
Dim Largest As Long
    Dim Smallest As Long
    Dim FirstPlace As Range
    Dim LastPlace As Range

    Largest = WorksheetFunction.Max(Range("O5:O25"))
    Smallest = WorksheetFunction.Min(Range("O5:O25"))

    Range("A18").Value = Largest
    Range("A19").Value = Smallest

    Set FirstPlace = Range("O5:O25").Find(what:=Largest, LookAt:=xlWhole)
    Set LastPlace = Range("O5:O25").Find(what:=Smallest, LookAt:=xlWhole)

    Range("B18").Value = FirstPlace.Address
    Range("B19").Value = LastPlace.Address



End Sub


(pieced together from internet research) to find the smallest value and highest value within the range but it only a) rounds to 0 decimal place and b) says run time error 91 object variable or with block variable not set ( but only for the last bit of the code Range("B19").Value = LastPlace.Address

[Captain Sensible]

a) only rounds to 1 decimal because you declared the variable as Long, should be declared as Double if you wanted decimals.

b) run time error 91 usually occurs where the oject doesn't exist, can't see any error from the code you posted up, maybe you need to step through it debugging and checking what values your variables are holding.

[cjones198]

Thanks Captain!!