I'm work a lot with variant data type, and I need a method to find the min
value in a column with use of a function. I know that worksheetfunction can
be used on a variant. Is there a function that allow me to adress only one
colum or row in a variant datatype.

Regards
Gunnar Aronsen
Trønder Energi Kraft AS
Norway

Can you be more specific on what you want to do?

"Gunnar Aronsen" wrote:

I'm work a lot with variant data type, and I need a method to find the min
value in a column with use of a function. I know that worksheetfunction can
be used on a variant. Is there a function that allow me to adress only one
colum or row in a variant datatype.

Regards
Gunnar Aronsen
Trønder Energi Kraft AS
Norway

I have a variant which has several columns. I want to find the min and
maxvalue in one of these colum.

What I want to do is analoge to use this function:

MinValue=worksheetfunction.Min(MyRange.columns(2))

I also found out that this function is working on a variant datatype, but
only on the whole array.

Regard
Gunnar

So you have a range myRange that covers multiple columns and you want to find
the minimum of just a couple of columns of the range.

I think you'll have to define another range for those columns OR do
something like this:

Dim myRange As Range
Dim r As Range


Set myRange = Range("A1:D20")

minimum = 999999
For Each r In myRange
Debug.Print r.Address, r.Value, minimum
If r.Column = 2 Or r.Column = 3 Then
minimum = WorksheetFunction.Min(minimum, r.Value)
End If
Next r

End Sub


"Gunnar Aronsen" wrote:

I have a variant which has several columns. I want to find the min and
maxvalue in one of these colum.

What I want to do is analoge to use this function:

MinValue=worksheetfunction.Min(MyRange.columns(2))

I also found out that this function is working on a variant datatype, but
only on the whole array.

Regard
Gunnar

Hi Gunnar,

Try something like:

'=============
Public Function ArrMin( _
Arr As Variant, _
iDimensionn As Long) As Variant
Dim i As Long

For i = LBound(Arr, 1) To UBound(Arr, 1)
If Not IsEmpty(Arr(i, iDimensionn)) Then
If IsEmpty(ArrMin) Then
ArrMin = Arr(i, iDimensionn)
ElseIf ArrMin Arr(i, iDimensionn) Then
ArrMin = Arr(iDimensionn, i)
End If
End If
Next i
End Function
'<<=============


For example:

'=============
Public Sub TestA()
Dim vArr As Variant
Dim res As Variant

vArr = Range("A1:D100").Value

res = ArrMin(vArr, 3)
MsgBox res
End Sub
'<<=============


---
Regards,
Norman




"Gunnar Aronsen" <gaMISSINGATtronderenergiMISSINGDOTno wrote in message
...
I'm work a lot with variant data type, and I need a method to find the min
value in a column with use of a function. I know that worksheetfunction
can
be used on a variant. Is there a function that allow me to adress only one
colum or row in a variant datatype.

Regards
Gunnar Aronsen
Trønder Energi Kraft AS
Norway

Maybe using application.index to pick out each column in the array...

Option Explicit
Sub testme2()

Dim myArr As Variant
Dim iCtr As Long

myArr = ActiveSheet.Range("a1:J24")

For iCtr = LBound(myArr, 2) To UBound(myArr, 2)
With Application
MsgBox .Min(.Index(myArr, , iCtr))
End With
Next iCtr

End Sub




Gunnar Aronsen wrote:

I'm work a lot with variant data type, and I need a method to find the min
value in a column with use of a function. I know that worksheetfunction can
be used on a variant. Is there a function that allow me to adress only one
colum or row in a variant datatype.

Regards
Gunnar Aronsen
Trønder Energi Kraft AS
Norway

--

Dave Peterson

Thanks all for your help, but is there not build in function to adress a
singel column in a variant, such as it's in datatype range. I want to avoid
to make to many function one mye own. Prefer to use build in function.

Gunnar

Alan,

Time to check the date/timezone on your pc.



Alan Beban wrote:

Where arr is the name of your variant variable

Application.Min(Application.Index(arr, 0, n))

will find the minimum in the nth "column".

Alan Beban

Gunnar Aronsen wrote:
I'm work a lot with variant data type, and I need a method to find the min
value in a column with use of a function. I know that worksheetfunction can
be used on a variant. Is there a function that allow me to adress only one
colum or row in a variant datatype.

Regards
Gunnar Aronsen
Trønder Energi Kraft AS
Norway

--

Dave Peterson

But it's showing up as April 10th.

Alan Beban wrote:

I don't think so. I'm posting this at 4:54.

Dave Peterson wrote:
Alan,

Time to check the date/timezone on your pc.



Alan Beban wrote:
Where arr is the name of your variant variable

Application.Min(Application.Index(arr, 0, n))

will find the minimum in the nth "column".

Alan Beban

Gunnar Aronsen wrote:
I'm work a lot with variant data type, and I need a method to find the min
value in a column with use of a function. I know that worksheetfunction can
be used on a variant. Is there a function that allow me to adress only one
colum or row in a variant datatype.

Regards
Gunnar Aronsen
Trønder Energi Kraft AS
Norway


--

Dave Peterson

Oh, that!:-)

Dave Peterson wrote:
But it's showing up as April 10th.

Alan Beban wrote:
I don't think so. I'm posting this at 4:54.

Dave Peterson wrote:
Alan,

Time to check the date/timezone on your pc.



Alan Beban wrote:
Where arr is the name of your variant variable

Application.Min(Application.Index(arr, 0, n))

will find the minimum in the nth "column".

Alan Beban

Gunnar Aronsen wrote:
I'm work a lot with variant data type, and I need a method to find the min
value in a column with use of a function. I know that worksheetfunction can
be used on a variant. Is there a function that allow me to adress only one
colum or row in a variant datatype.

Regards
Gunnar Aronsen
Trønder Energi Kraft AS
Norway

Where arr is the name of your variant variable

Application.Min(Application.Index(arr, 0, n))

will find the minimum in the nth "column".

Alan Beban

Gunnar Aronsen wrote:
I'm work a lot with variant data type, and I need a method to find the min
value in a column with use of a function. I know that worksheetfunction can
be used on a variant. Is there a function that allow me to adress only one
colum or row in a variant datatype.

Regards
Gunnar Aronsen
Trønder Energi Kraft AS
Norway

I don't think so. I'm posting this at 4:54.

Dave Peterson wrote:
Alan,

Time to check the date/timezone on your pc.



Alan Beban wrote:
Where arr is the name of your variant variable

Application.Min(Application.Index(arr, 0, n))

will find the minimum in the nth "column".

Alan Beban

Gunnar Aronsen wrote:
I'm work a lot with variant data type, and I need a method to find the min
value in a column with use of a function. I know that worksheetfunction can
be used on a variant. Is there a function that allow me to adress only one
colum or row in a variant datatype.

Regards
Gunnar Aronsen
Trønder Energi Kraft AS
Norway