How to work with only one colum of a variant
 

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

How to work with only one colum of a variant
 

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

How to work with only one colum of a variant
 

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

How to work with only one colum of a variant
 

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

How to work with only one colum of a variant
 

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

How to work with only one colum of a variant
 

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

How to work with only one colum of a variant
 

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

How to work with only one colum of a variant
 

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

How to work with only one colum of a variant
 

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

How to work with only one colum of a variant
 

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

How to work with only one colum of a variant
 

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

How to work with only one colum of a variant
 

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