How to find a max value in an array?

[troelsi]

Hi

Is it possible to find a max value in a to dimensional array using worksheetfunction.max?

Or this there any other simple vay instead of using loops?

thank you

Troels Isaksen

[mdmackillop]

Hi Troelsi,
Welcome to VBAX.
The Max function should work


Sub FMax()
Dim arr
arr = Range("Data").Value
MsgBox Application.WorksheetFunction.Max(arr)
End Sub

[troelsi]

thanks for you answer.

what if Im looking for a max value along one of the dimension only.


redim p(t+1,n)
For j = 0 To T
For k = 1 To N
p(j, k) = Range("b6").Offset(j, k)
Next
Next
For k = N To 1 Step -1
x(N) = WorksheetFunction.Max(p(for j = 0 to T, k))
Next

This doesn't work for me, what should i replace the "for j= to T" with?

thank you

Troels Isaksen

[Bob Phillips]

ReDim x(1 To N)
For k = 1 To N
x(k) = Application.Max(Application.Transpose(Range("B6").Offset(0, k).Resize(T + 1)))
Next

[troelsi]

Thanks for your answer, that's a nice sollution.

But I'm looking for a more general sollution. If I don't want to use the range method, how can I then do it?

Best regards

Troels Isaksen

[Bob Phillips]

Thanks for your answer, that's a nice sollution.

But I'm looking for a more general sollution. If I don't want to use the range method, how can I then do it?

Best regards

Troels Isaksen

Elaborate by what you mean by not wanting to use the Range method, and why not.

[troelsi]

At the bottom of this code I want to find the max value for all k's in fst(i,k) for values of i = 1. Is that possible to do without transforming it into a one-dimensional array?


For i = N To 1 Step -1
For j = 0 To T
For k = j To T
f(i, j, k) = p(j, i) + fst(i + 1, k - j)
If f(i, j, k) >= fst(i, k) Then
fst(i, k) = f(i, j, k)
x(i, k) = j
End If
Next
Next
Next
For k = 0 To T
arr(1) = fst(1, k)
Next
fstar(1) = Application.Max(arr())