Using formulae correctly

Hi all,

I have a slight problem with the correct formatting of my code. I'm trying to get VBA to find the cell with the largest magnitude, then report the address of that cell which will be used for a copy and paste method. I have created a function to find the largest magnitude which produces a number, and another function to report the address which is a range. Unfortunately I keep getting errors relating to the second function and VBA tells me there's a run-time error 424: object required. I would be grateful if anyone could shed some light on this problem for me.

Thanks
J

PHP Code: 

Function MaxMagNum(rng As Range) As Double
        If Sqr((WorksheetFunction.Max(rng)) ^ 2) >= Sqr((WorksheetFunction.Min(rng)) ^ 2) Then
            MaxMagNum = WorksheetFunction.Max(rng)
        ElseIf Sqr((WorksheetFunction.Max(rng)) ^ 2) < Sqr((WorksheetFunction.Min(rng)) ^ 2) Then
            MaxMagNum = WorksheetFunction.Min(rng)
        End If
    End Function
Function MaxMagAddress(rng As Range) As Range
        'MsgBox MaxMagNum(rng).Address
        Set MaxMagAddress = rng.Cells(WorksheetFunction.Index(rng, WorksheetFunction.Match(MaxMagNum(rng), rng, 0))).Address
End Function 


Yes, the double returned by MaxMagNum is used in the match formula. The idea is that MaxMagAddress will be able to return the range information for that cell, but the error object required keeps cropping up.

OK, I see what you're saying, but how can I return the range of the min or max cell?

I've had a play around with the code and tried to condense the function down into one, however I still get "Object required". Please excuse my ignorance but I only occasionally use VBA and I'm struggling to see how to fix this.

Thanks
J

PHP Code: 

Function MaxMagAddress(rng As Range) As Range
        If Sqr((WorksheetFunction.Max(rng)) ^ 2) >= Sqr((WorksheetFunction.Min(rng)) ^ 2) Then
            Set MaxMagAddress = rng.Cells(WorksheetFunction.Index(rng, WorksheetFunction.Match(WorksheetFunction.Max(rng), rng, 0))).Address
        Else: MaxMagAddress = rng.Cells(WorksheetFunction.Index(rng, WorksheetFunction.Match(WorksheetFunction.Min(rng), rng, 0))).Address
        End If
    End Function 


Ok, tried that and I'm still getting the "object required" error. I have provided a larger excerpt from my macro which shows how the function slots into the sub. It does seem like there's still a problem with range provided. In the sub, I've defined a term called dx as a range object which gets substituted into the function; do you think this is compatible with the formula I've used in the function which uses the cells property?

Thanks
J

PHP Code: 

Function MaxMagAddress(rng As Range) As Range
        If Sqr((WorksheetFunction.Max(rng)) ^ 2) >= Sqr((WorksheetFunction.Min(rng)) ^ 2) Then
            Set MaxMagAddress = rng.Cells(WorksheetFunction.Match(WorksheetFunction.Max(rng), rng, 0)).Address
        Else: Set MaxMagAddress = rng.Cells(WorksheetFunction.Match(WorksheetFunction.Min(rng), rng, 0)).Address
        End If
    End Function

Private Sub OKButton_Click()
    Dim d As Variant
    Dim WorkArea As Range
        Set WorkArea = Worksheets(1).Range("Z2:AI17")
    Dim dx As Range
        Set dx = Worksheets(1).Range("AB2:AB10")

    For Each d In Joint
        'copy loadcase C2 to work area
        If Worksheets(1).Cells(d.Row, d.Offset(0, -1).Column).Value = "C2" Then
            Worksheets(1).Range(Cells(d.Row, d.Offset(0, -1).Column), Cells(d.Row, d.Offset(0, 10).Column)).Copy _
            Destination:=Worksheets(1).Range("Z" & Rows.Count).End(xlUp).Offset(1, 0)
        End If
        'copy loadcase C3 to work area
        If Worksheets(1).Cells(d.Row, d.Offset(0, -1).Column).Value = "C3" Then
            Worksheets(1).Range(Cells(d.Row, d.Offset(0, -1).Column), Cells(d.Row, d.Offset(0, 10).Column)).Copy _
            Destination:=Worksheets(1).Range("Z" & Rows.Count).End(xlUp).Offset(1, 0)
        End If
         If d.Offset(1, 0).Value > d.Value Then
              
                MaxMagAddress(dx).Copy _
                Destination:=Worksheets(1).Range("O" & Rows.Count).End(xlUp).Offset(1, 0)
            
        WorkArea.ClearContents
        End If

    Next d

End Sub 


Here's the sample file. I've trimmed it down somewhat as it normally has multiple sheets. The code is a bit of a jumble because as I make mistakes or find new ways of writing some code, I'm reluctant to delete the old code before it all works in case I lose something!

Basically, the code is intended to slim down the information contained in the leftmost columns. Under the loadcase columns, there are cases such as C2, C3, C12 and C13 which can be collapsed down into one loadcase, but the worst of each single loadcase must be taken for the new collapsed loadcase. Hence, I have a work area where the relevant info is extracted to, the min and max criteria is applied, and the idea is to copy those min or max cells accross to a new location. The whole process is then looped down the leftmost columns (only 2000 rows at this stage).

Once its all finished I hope to have a source workbook and a destination workbook so the information is kept completely separate, but I'll attack that once I've got the copying element sorted out.

Thanks
J

OK I tried the code you suggested but it turned out that the value obtained in the specified range wasn't being returned at all, so all columns except the first two (which are populated separately) remained blank.

I did some research and found some advice on the MSDN website for returning the cell address of minimum or maximum values, and I've altered the function code as you can see below. However, when I run it I get a run-time error 91; object variable not set, relating to line MaxMagAddress = cell.address. I noticed that on the website, the name differed from mine in that they did not set the function as a range object; however when I try this I get another error in the sub where I've tried to copy what the function returns with another object related error.

I just seem to be going around in circles. All I want is for a range object to be returned by the function such that I can copy what is at that range. It doesn't sound that hard, but it is for me.

PHP Code: 

Function MaxMagAddress(rng As Range) As Range
        If Sqr((WorksheetFunction.Max(rng)) ^ 2) >= Sqr((WorksheetFunction.Min(rng)) ^ 2) Then
            MaxMagNum = Sqr((WorksheetFunction.Max(rng)) ^ 2)
        Else
            MaxMagNum = Sqr((WorksheetFunction.Min(rng)) ^ 2)
        End If
        For Each cell In rng
            If cell = MaxMagNum Then
                MaxMagAddress = cell.Address
                Exit For
            End If
        Next cell       
End Function 


That formulae does the same thing as using the abs() function to find absolute values. This is not the problem as a quick msgbox check shows that the correct value has been identified and returned by MaxMagNum.

The code I've written above does just that i.e. it loops through the range entered into the function to find the cell that is equivalent to the absolute maximum. The problem persists and I think its to do with the use of the Address property, which does not seem to be returning a range object.

Thanks Norie. That's great. Actually I've got the other method to work as well (see code below), when I finally worked out that .address does not return a range object!

Thanks for persevering with me.

PHP Code: 

Function MaxMagAddress(rng As Range) As Range
            If Sqr((WorksheetFunction.Max(rng)) ^ 2) >= Sqr((WorksheetFunction.Min(rng)) ^ 2) Then
            MaxMagNum = WorksheetFunction.Max(rng)
        Else
            MaxMagNum = WorksheetFunction.Min(rng)
        End If
        Set MaxMagAddress = rng.Cells(WorksheetFunction.Match(MaxMagNum, rng, 0))
    End Function