How to work with only one colum of a variant

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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("A120")

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

 

[Norman Jones]

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("A1100").Value

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

 

[Dave Peterson]

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

 

[Guest]

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

 

[Dave Peterson]

Alan,

Time to check the date/timezone on your pc.

 

[Dave Peterson]

But it's showing up as April 10th.

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

 

[Alan Beban]

Oh, that!

But it's showing up as April 10th.

 

[Alan Beban]

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

 

[Alan Beban]

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

Alan,

Time to check the date/timezone on your pc.