x and y are single column ranges of the same size. How do I retrieve
the corresponding values to x & from y as I loop through x? See
example below and thanks for any help.

Function offset(x As Range, y As Range, test As String)

For Each icell In x
If icell.Value = test Then
' I want to use the value of the corresponding element of range y here
End If
Next

End Function

--
Mo

Hi Mo,

Try something like,
'============
Function Myoffset(x As Range, y As Range, test As String)
Dim i As Long

If x.Count < y.Count Then Exit Function

For i = 1 To x.Count
With x(i)
If .Value = test Then .Value = y(i).Value
End With
Next
End Function
'============

---
Regards,
Norman



"mo childs" wrote in message
...
x and y are single column ranges of the same size. How do I retrieve
the corresponding values to x & from y as I loop through x? See
example below and thanks for any help.

Function offset(x As Range, y As Range, test As String)

For Each icell In x
If icell.Value = test Then
' I want to use the value of the corresponding element of range y here
End If
Next

End Function

--
Mo

Hi

When all values in x are unique, then simply:
ReturnValue=Application.WorksheetFunction.VLookup( icell,x:y,2,0)

NB! offset is built-in function, so don't use it as an UDF name! When values
in x aren't unique, then:

Function YourFunc(x As Range, y As Range, test As String)
YourFunc = ""
i = 0
For Each icell In x
i = i + 1
If icell.Value = test Then YourFunc = y(i)
Next
End Function


Arvi Laanemets


"mo childs" wrote in message
...
x and y are single column ranges of the same size. How do I retrieve
the corresponding values to x & from y as I loop through x? See
example below and thanks for any help.

Function offset(x As Range, y As Range, test As String)

For Each icell In x
If icell.Value = test Then
' I want to use the value of the corresponding element of range y here
End If
Next

End Function

--
Mo

Offset = Range("B5:B9").Column - Range("D5:D9").Column

Kaa

--
Kaa
-----------------------------------------------------------------------
Kaak's Profile: http://www.excelforum.com/member.php...nfo&userid=751
View this thread: http://www.excelforum.com/showthread.php?threadid=47742

That's just what I wanted. Thanks very much

Kaak wrote:

Offset = Range("B5:B9").Column - Range("D5:D9").Column



--
Mo