This portion:
Range("A1").Activate
StartCell = ActiveCell.Offset(0, 0).Address
EndCell = ActiveCell.Offset(0, 2).Address
Range(StartCell, EndCell).Select
is really pointing at A1:C1 (not A1:A3)
..offset(0,2) is 2 columns to the right.
And you may want to use xlwhole instead of xlpart, too.
====
And you may want to try it without selecting ranges:
Option Explicit
Sub FindMax()
Dim StartCell As Range
Dim EndCell As Range
Dim WorkRange As Range
Dim MaxVal As Double
Dim FoundCell As Range
Set StartCell = Range("a1").Offset(0, 0)
Set EndCell = Range("a1").Offset(0, 2)
Set WorkRange = Range(StartCell, EndCell)
MaxVal = Application.Max(WorkRange)
Set FoundCell = WorkRange.Find(What:=MaxVal, _
After:=WorkRange.Range("A1"), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
MsgBox "Max value was not found: " & MaxVal
Else
MsgBox "Found it at: " & _
FoundCell.Address(0, 0) & " = " & FoundCell.Value
End If
End Sub
(
Mcneilius wrote:
>
> Hi all
>
> I'm trying to get a macro to find and select cell that contains the
> maximum value of 3 selected cells.
>
> Say cells A1 to A3 contain 2.45, 3.36, 2.78 (these numbers can change),
> the macro currently looks like:
>
> Sub FindMax()
>
> Range("A1").Activate
> StartCell = ActiveCell.Offset(0, 0).Address
> EndCell = ActiveCell.Offset(0, 2).Address
> Range(StartCell, EndCell).Select
>
> Dim WorkRange As Range
>
> Set WorkRange = Selection
>
> MaxVal = Application.Max(WorkRange)
>
> On Error Resume Next
> Workrange.Find(What:=MaxVal, _
> After:=Workrange.Range("A1"), _
> LookIn:=xlValues, _
> LookAt:=xlPart, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, MatchCase:=False _
> ).Select
> If Err <> 0 Then MsgBox "Max value was not found: " & MaxVal
>
> End Sub
>
> The code seems to work well on some selections, and not on others.
> Sometimes there is a unique maximum of, say, 3.36 and it will say "Max
> Value was not found: 3.36" !?!?
>
> Please, does anyone have any ideas on where I'm going wrong?
>
> Thanks for your time
>
> Neil
>
> --
> Mcneilius
> ------------------------------------------------------------------------
> Mcneilius's Profile: http://www.excelforum.com/member.php...o&userid=13776
> View this thread: http://www.excelforum.com/showthread...hreadid=468552
--
Dave Peterson
Bookmarks