dgs2001
04-21-2013, 10:51 AM
Hello All
I am stumped :dunno
I use the following code in a custom function, I pass into this function a dynamic range which is always 75 columns wide but varies in number of rows up to a maximum of 40, the number of rows in this range is passed into my function as value N.
The function should look for a value (passed in as "highest") in a specific column (passed in as "col") within the entire range then return just the row containing that highest number.
Function getPotential(v2 As Variant, N As Integer, highest As Double, col As Integer) As Range
Dim v As Variant, p As Variant
Dim i As Integer, j As Integer
Set v = v2
Set p = Nothing
For i = 3 To N
If v(i, col) = highest Then
''' I expect this next line to set range p to a single row from range v
Set p = Application.Index(v, i, 0)
''' However it doesnt, it sets p to all rows of v starting at i
End If
Next i
Set getPotential = p
End Function
The problem I'm having is with this line
Set p = Application.Index(v, i, 0)
The resulting output is not a single row by 75 column range but all rows from the original range starting at the row i
Please help as I'm starting to pull out clumps of hair !!
Thanks in advance Dgs2001
I am stumped :dunno
I use the following code in a custom function, I pass into this function a dynamic range which is always 75 columns wide but varies in number of rows up to a maximum of 40, the number of rows in this range is passed into my function as value N.
The function should look for a value (passed in as "highest") in a specific column (passed in as "col") within the entire range then return just the row containing that highest number.
Function getPotential(v2 As Variant, N As Integer, highest As Double, col As Integer) As Range
Dim v As Variant, p As Variant
Dim i As Integer, j As Integer
Set v = v2
Set p = Nothing
For i = 3 To N
If v(i, col) = highest Then
''' I expect this next line to set range p to a single row from range v
Set p = Application.Index(v, i, 0)
''' However it doesnt, it sets p to all rows of v starting at i
End If
Next i
Set getPotential = p
End Function
The problem I'm having is with this line
Set p = Application.Index(v, i, 0)
The resulting output is not a single row by 75 column range but all rows from the original range starting at the row i
Please help as I'm starting to pull out clumps of hair !!
Thanks in advance Dgs2001