vba arithmetic between ranges without looping thru each item

[jalea148]

Consider the function xyz

Function xyz(Opn As Range, Clo As Range, Wts As Range)
Dim MaxOpnClo As Range
    MaxOpnClo = Application.WorksheetFunction.Max(Opn, Clo)
    xyz= Application.WorksheetFunction.SumProduct(MaxOpnClo, Wts) / Application.WorksheetFunction.Sum(Wts)
    
End Function

As written, this code does not work.

Presumably MaxOpnClo = max(Opn,Clo), ...

 

[RickXL]

Hi,

A few questions, if I may, please:

Why don't you want to use a loop?
Will this be a User Defined Function that you will use on a Worksheet?
Isn't Application.WorksheetFunction.SumProduct(MaxOpnClo, Wts) / Application.WorksheetFunction.Sum(Wts) just MaxOpnClo?

Thanks,

 

[Peter_SSs]

Also, what are you trying to do with those 2 blue lines?
Max will return a numerical value. You can't set that to be a Range object.

Function xyz(Opn As Range, Clo As Range, Wts As Range)
[COLOR="#0000CD"]Dim MaxOpnClo As Range
    MaxOpnClo = Application.WorksheetFunction.Max(Opn, Clo)[/COLOR]
    xyz= Application.WorksheetFunction.SumProduct(MaxOpnClo, Wts) / Application.WorksheetFunction.Sum(Wts)
    
End Function

Tell us more about what you are actually trying to achieve with this function and what your data is like that you want to use it on.

 

[jalea148]

I finally resolved the problem by going to loops. Max between 2 identically dimensioned ranges is the essence of my question. Think how much cleaner and less prone to error it would be if the operation on the range object could be treated aS in linear algebra.
Thanx for responding.

 

[RickXL]

But this does work?

Sub Test()
    Dim MaxOpnClo As Double
    Dim Opn As Range
    Dim Clo As Range
    
    Set Opn = Range("A1:C1")
    Set Clo = Range("A2:C2")
    MaxOpnClo = Application.WorksheetFunction.Max(Opn, Clo)
End Sub

 

[jalea148]

But this does work?

Sub Test()
    Dim MaxOpnClo As Double
    Dim Opn As Range
    Dim Clo As Range
    
    Set Opn = Range("A1:C1")
    Set Clo = Range("A2:C2")
    MaxOpnClo = Application.WorksheetFunction.Max(Opn, Clo)
End Sub

Your example finds the max of the 6 cells. My example, below, fails. I want a new range with its 1st cell the max of the 1st pair of cells from Opn, Clo; 2nd cell the max of the 2nd pair of cells from Opn, Clo; ...

Function gmax(Opn As Range, Clo As Range, Wts As Range)
Dim MaxOpnClo As Range, WtsA() As Variant
Dim I As Integer, WtsSize As Long, SumWta As Long

WtsA = Wts
WtsSize = UBound(WtsA, 1)

SumWta = WtsSize * (WtsSize + 1) / 2
Debug.Print WtsSize, SumWta

MaxOpnClo = Application.WorksheetFunction.Max(Opn, Clo)
Debug.Print "hi"
.................
End Function

Debug.Print WtsSize, SumWta is correct.
Debug.Print "hi" does not print
It's necessary to loop through the individual pairs.

 

[RickXL]

Yes, the obvious way would be to use a loop.

regards,

 

[Macropod]

Cross-posted at: https://www.excelguru.ca/forums/sho...between-ranges-without-looping-thru-each-item
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html