Problem with a function that calls itself
 

Hi,

I created a function that emulates the Excel function LOOKUP but that
does not require the lookup table to be sorted.

It works fine, except when the first parameter is a call to itself,
that is, when nesting the function.

For example:

In M27:M31 if I input the array function:
={vslookup(C6:C10;H6:H8;J6:J8)} 'it works fine

In N27:N31 if I input the array function:
={vslookup(M27:M31;W26:W27;X26:X27)} 'it works fine (the first
argument is the result of the previous function)

However, if I nest the 2 formulas in O27:O31::
={vslookup(vslookup(C6:C10;H6:H8;J6:J8);W26:W27;X2 6:X27)} ' I get
#VALUE!

What is the problem with nesting?


Function VSLookup(a, b, c)
' a, b y c son rangos verticales
' el número de filas de b y c es el mismo
Dim i, j, nrows
ReDim T(a.Rows.Count, 1)
For i = 1 To a.Rows.Count
For j = 1 To b.Rows.Count
If a(i, 1) = b(j, 1) Then
T(i, 1) = c(j, 1)
GoTo aqui
End If
Next j
T(b.Rows.Count, 1) = CVErr(xlErrNA)

aqui:
Next i
VSLookup = T
End Function

Problem with a function that calls itself
 

I think you have a couple of problems in your nested function.

#1. In your first nest, you're passing 3 ranges (a, b, c are all range
variables). The second time through, that a variable is not a range--it's an
array (a single column vector).

#2. You're comparing the value of a cell against that xlerrna and that won't
work.

I don't quite understand what your function is doing, but I think it would be
easiest to process the data as elements of an array--instead of cells in a
range.

I think I'd pass a variant and check to see if it's a range. If it's a range,
then convert it to an array.

I didn't do that here (I only did the first parm).

#3. I don't like the Goto. There are ways around it. So I changed it.

This may get you closer:

Option Explicit
Function VSLookup(a As Variant, b As Range, c As Range) As Variant

Dim i As Long
Dim j As Long
Dim FoundAMatch As Boolean
Dim RowsInB As Long

If TypeName(a) = "Range" Then
'make it a single column array
a = Application.Transpose(a.Value)
End If

RowsInB = b.Rows.Count

ReDim T(LBound(a) To UBound(a))

For i = LBound(a) To UBound(a)
FoundAMatch = False
For j = 1 To RowsInB
If a(i) = b(j, 1) Then
T(i) = c(j, 1)
FoundAMatch = True
Exit For
End If
Next j
If FoundAMatch = True Then
'do nothing
Else
T(RowsInB) = "xlErrNA"
End If
Next i
VSLookup = T

End Function




vsoler wrote:

Hi,

I created a function that emulates the Excel function LOOKUP but that
does not require the lookup table to be sorted.

It works fine, except when the first parameter is a call to itself,
that is, when nesting the function.

For example:

In M27:M31 if I input the array function:
={vslookup(C6:C10;H6:H8;J6:J8)} 'it works fine

In N27:N31 if I input the array function:
={vslookup(M27:M31;W26:W27;X26:X27)} 'it works fine (the first
argument is the result of the previous function)

However, if I nest the 2 formulas in O27:O31::
={vslookup(vslookup(C6:C10;H6:H8;J6:J8);W26:W27;X2 6:X27)} ' I get
#VALUE!

What is the problem with nesting?

Function VSLookup(a, b, c)
' a, b y c son rangos verticales
' el número de filas de b y c es el mismo
Dim i, j, nrows
ReDim T(a.Rows.Count, 1)
For i = 1 To a.Rows.Count
For j = 1 To b.Rows.Count
If a(i, 1) = b(j, 1) Then
T(i, 1) = c(j, 1)
GoTo aqui
End If
Next j
T(b.Rows.Count, 1) = CVErr(xlErrNA)

aqui:
Next i
VSLookup = T
End Function

--

Dave Peterson

Problem with a function that calls itself
 

On Sep 13, 3:53*am, Dave Peterson wrote:
I think you have a couple of problems in your nested function.

#1. *In your first nest, you're passing 3 ranges (a, b, c are all range
variables). *The second time through, that a variable is not a range--it's an
array (a single column vector).

#2. *You're comparing the value of a cell against that xlerrna and that won't
work.

I don't quite understand what your function is doing, but I think it would be
easiest to process the data as elements of an array--instead of cells in a
range. *

I think I'd pass a variant and check to see if it's a range. *If it's a range,
then convert it to an array.

I didn't do that here (I only did the first parm).

#3. *I don't like the Goto. *There are ways around it. *So I changed it.

This may get you closer:

Option Explicit
Function VSLookup(a As Variant, b As Range, c As Range) As Variant

* * Dim i As Long
* * Dim j As Long
* * Dim FoundAMatch As Boolean
* * Dim RowsInB As Long

* * If TypeName(a) = "Range" Then
* * * * 'make it a single column array
* * * * a = Application.Transpose(a.Value)
* * End If

* * RowsInB = b.Rows.Count

* * ReDim T(LBound(a) To UBound(a))

* * For i = LBound(a) To UBound(a)
* * * * FoundAMatch = False
* * * * For j = 1 To RowsInB
* * * * * * If a(i) = b(j, 1) Then
* * * * * * * * T(i) = c(j, 1)
* * * * * * * * FoundAMatch = True
* * * * * * * * Exit For
* * * * * * End If
* * * * Next j
* * * * If FoundAMatch = True Then
* * * * * * 'do nothing
* * * * Else
* * * * * * T(RowsInB) = "xlErrNA"
* * * * End If
* * Next i
* * VSLookup = T

End Function



vsoler wrote:

Hi,

I created a function that emulates the Excel function LOOKUP but that
does not require the lookup table to be sorted.

It works fine, except when the first parameter is a call to itself,
that is, when nesting the function.

For example:

In M27:M31 if I input the array function:
={vslookup(C6:C10;H6:H8;J6:J8)} * * * 'it works fine

In N27:N31 if I input the array function:
={vslookup(M27:M31;W26:W27;X26:X27)} * * * 'it works fine (the first
argument is the result of the previous function)

However, if I nest the 2 formulas in O27:O31::
={vslookup(vslookup(C6:C10;H6:H8;J6:J8);W26:W27;X2 6:X27)} * *' I get
#VALUE!

What is the problem with nesting?

Function VSLookup(a, b, c)
' a, b y c son rangos verticales
' el número de filas de b y c es el mismo
Dim i, j, nrows
ReDim T(a.Rows.Count, 1)
For i = 1 To a.Rows.Count
* For j = 1 To b.Rows.Count
* * If a(i, 1) = b(j, 1) Then
* * * T(i, 1) = c(j, 1)
* * * GoTo aqui
* * End If
* Next j
* T(b.Rows.Count, 1) = CVErr(xlErrNA)

aqui:
Next i
VSLookup = T
End Function

--

Dave Peterson

Thank you Dave, I'm studying your code that seems to me of great help.

#1 - Yes, the origin of my problem was the confusion between a Range
and an Array. The Function has to be able to handle either one, and
that's what your code does.

#2 - I'm not comparing two values, as you say. I'm just assigning an
#N/A value to an element of my T array, because a value of the
original "a" Range was not found in the "b" lookup table.

#3 - I accept your comment on the GoTo.

What I am going to do now is to test your code. However, there is one
small piece of it that I do not understand. If my original "a" Range
is already a vertical column, why do I have to transpose it? I would
like that it remains a vertical array. Perhaps you can explain me
that.

Thank you for your help Dave.

Vicente Soler

Problem with a function that calls itself
 

Create a new procedu

Dim myRng as Range
dim myVal1 as variant
dim myVal2 as variant
Set myrng = activesheet.range("A1:A4")
myval1 = myrng.value
myval2 = application.transpose(myrng.value)
Stop

Set a watch for myVal1 and myVal2.
run the code

When the procedure stops, you can look at each of the watched variables.

You'll see that myVal1 is a 4 row by 1 column (2 dimensional) array.
But myval2 is a single dimension array/vector.

It would by like defining two vars:

dim Var1(1 to 4, 1 to 1) as variant
and
dim Var2(1 to 4) as variant







vsoler wrote:

On Sep 13, 3:53 am, Dave Peterson wrote:
I think you have a couple of problems in your nested function.

#1. In your first nest, you're passing 3 ranges (a, b, c are all range
variables). The second time through, that a variable is not a range--it's an
array (a single column vector).

#2. You're comparing the value of a cell against that xlerrna and that won't
work.

I don't quite understand what your function is doing, but I think it would be
easiest to process the data as elements of an array--instead of cells in a
range.

I think I'd pass a variant and check to see if it's a range. If it's a range,
then convert it to an array.

I didn't do that here (I only did the first parm).

#3. I don't like the Goto. There are ways around it. So I changed it.

This may get you closer:

Option Explicit
Function VSLookup(a As Variant, b As Range, c As Range) As Variant

Dim i As Long
Dim j As Long
Dim FoundAMatch As Boolean
Dim RowsInB As Long

If TypeName(a) = "Range" Then
'make it a single column array
a = Application.Transpose(a.Value)
End If

RowsInB = b.Rows.Count

ReDim T(LBound(a) To UBound(a))

For i = LBound(a) To UBound(a)
FoundAMatch = False
For j = 1 To RowsInB
If a(i) = b(j, 1) Then
T(i) = c(j, 1)
FoundAMatch = True
Exit For
End If
Next j
If FoundAMatch = True Then
'do nothing
Else
T(RowsInB) = "xlErrNA"
End If
Next i
VSLookup = T

End Function



vsoler wrote:

Hi,

I created a function that emulates the Excel function LOOKUP but that
does not require the lookup table to be sorted.

It works fine, except when the first parameter is a call to itself,
that is, when nesting the function.

For example:

In M27:M31 if I input the array function:
={vslookup(C6:C10;H6:H8;J6:J8)} 'it works fine

In N27:N31 if I input the array function:
={vslookup(M27:M31;W26:W27;X26:X27)} 'it works fine (the first
argument is the result of the previous function)

However, if I nest the 2 formulas in O27:O31::
={vslookup(vslookup(C6:C10;H6:H8;J6:J8);W26:W27;X2 6:X27)} ' I get
#VALUE!

What is the problem with nesting?

Function VSLookup(a, b, c)
' a, b y c son rangos verticales
' el número de filas de b y c es el mismo
Dim i, j, nrows
ReDim T(a.Rows.Count, 1)
For i = 1 To a.Rows.Count
For j = 1 To b.Rows.Count
If a(i, 1) = b(j, 1) Then
T(i, 1) = c(j, 1)
GoTo aqui
End If
Next j
T(b.Rows.Count, 1) = CVErr(xlErrNA)

aqui:
Next i
VSLookup = T
End Function

--

Dave Peterson

Thank you Dave, I'm studying your code that seems to me of great help.

#1 - Yes, the origin of my problem was the confusion between a Range
and an Array. The Function has to be able to handle either one, and
that's what your code does.

#2 - I'm not comparing two values, as you say. I'm just assigning an
#N/A value to an element of my T array, because a value of the
original "a" Range was not found in the "b" lookup table.

#3 - I accept your comment on the GoTo.

What I am going to do now is to test your code. However, there is one
small piece of it that I do not understand. If my original "a" Range
is already a vertical column, why do I have to transpose it? I would
like that it remains a vertical array. Perhaps you can explain me
that.

Thank you for your help Dave.

Vicente Soler

--

Dave Peterson