How to work with only one colum of a variant

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
'<<=============
 
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
 
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
 
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
 
Back
Top