UDF behaving very oddly -assistance or please suggest alternative.

P

PBcorn

I am using a UDF (code at bottom) to look up particular combinations of 2
column headers and return a range from below them. so where Avars = say, A,B,
and Product = say, Q,W,E,R,T,Y,.... (this UDF is then inserted in place of
a particular range argument in existing sumproduct formulae.)

Avars is a string argument in the function

Product is a range (a cell - column header in the column where the formula
is used)

source table:

A A A A A A B B B B B B B
Q W E R T Y Q W E R T Y
[
Data ranges in here

]

The problem is that although the function correctly looks up the relevant
values in the first part of the table (Avars= A), for the second part (Avars
= B), it seems unable to find some combinations, e.g. BQ, BR and BT and
therefore cannot return the ranges below them. The strange thing is that it
finds BW and BE without any problem.
I have tested the lookup code in a sub, it works fine. I have tested all the
arguments in the function.
Please help or suggest an alternative way of doing this.

Thanks

Function REFR(Product As Range, Colhead As Range, Avars As String)

Application.Volatile

Dim c As Range
Dim cs As Integer
Dim p As Integer
Dim sumthese()

p = Colhead.Columns.Count

For Each c In Colhead.Cells

If Trim(c.Value) = Trim(Product) And _
Trim(Cells((c.Row - 1), (c.Column)).Value) = Trim(Avars) Then
sumthese() = Range(Cells(c.Row + 1, c.Column), Cells(46, c.Column))
Else: cs = cs + 1
End If

Next c

If cs = p Then
REFR = 1
Else
REFR = sumthese
End If

End Function
 
T

Tom Ogilvy

Function REFR(Product As Range, Colhead As Range, Avars As String)

Application.Volatile

Dim c As Range
Dim cs As Integer
Dim p As Integer
Dim sumthese()

REFR = 1

bFound = False
For Each c In Colhead.Cells

If Trim(c.Value) = Trim(Product) And _
Trim(Cells((c.Row - 1), (c.Column)).Value) = Trim(Avars) Then
sumthese() = Range(Cells(c.Row + 1, c.Column), Cells(46, c.Column))
bFound = True
Exit for
End If

Next c

If bFound Then
REFR = sumthese
End If

End Function
 
P

PBcorn

Hi, thanks but this still doesn't work.

Tom Ogilvy said:
Function REFR(Product As Range, Colhead As Range, Avars As String)

Application.Volatile

Dim c As Range
Dim cs As Integer
Dim p As Integer
Dim sumthese()

REFR = 1

bFound = False
For Each c In Colhead.Cells

If Trim(c.Value) = Trim(Product) And _
Trim(Cells((c.Row - 1), (c.Column)).Value) = Trim(Avars) Then
sumthese() = Range(Cells(c.Row + 1, c.Column), Cells(46, c.Column))
bFound = True
Exit for
End If

Next c

If bFound Then
REFR = sumthese
End If

End Function

--
Regards,
Tom Ogilvy


PBcorn said:
I am using a UDF (code at bottom) to look up particular combinations of 2
column headers and return a range from below them. so where Avars = say, A,B,
and Product = say, Q,W,E,R,T,Y,.... (this UDF is then inserted in place of
a particular range argument in existing sumproduct formulae.)

Avars is a string argument in the function

Product is a range (a cell - column header in the column where the formula
is used)

source table:

A A A A A A B B B B B B B
Q W E R T Y Q W E R T Y
[
Data ranges in here

]

The problem is that although the function correctly looks up the relevant
values in the first part of the table (Avars= A), for the second part (Avars
= B), it seems unable to find some combinations, e.g. BQ, BR and BT and
therefore cannot return the ranges below them. The strange thing is that it
finds BW and BE without any problem.
I have tested the lookup code in a sub, it works fine. I have tested all the
arguments in the function.
Please help or suggest an alternative way of doing this.

Thanks

Function REFR(Product As Range, Colhead As Range, Avars As String)

Application.Volatile

Dim c As Range
Dim cs As Integer
Dim p As Integer
Dim sumthese()

p = Colhead.Columns.Count

For Each c In Colhead.Cells

If Trim(c.Value) = Trim(Product) And _
Trim(Cells((c.Row - 1), (c.Column)).Value) = Trim(Avars) Then
sumthese() = Range(Cells(c.Row + 1, c.Column), Cells(46, c.Column))
Else: cs = cs + 1
End If

Next c

If cs = p Then
REFR = 1
Else
REFR = sumthese
End If

End Function
Click to expand...
Click to expand...
 
T

Tom Ogilvy

Since you raised the issue, I set up a workbook as you describe and tested it
for all combinations.

All worked for me.

Based on my reading of your code, your header rows should be

Q W E R T Y Q W E R T Y
A A A A A A B B B B B B B

rather than
as you posted.

anyway, it worked for me - I tested my version, but my version was
consistent with your version as far as the header rows.

--
Regards,
Tom Ogilvy





PBcorn said:
Hi, thanks but this still doesn't work.

Tom Ogilvy said:
Function REFR(Product As Range, Colhead As Range, Avars As String)

Application.Volatile

Dim c As Range
Dim cs As Integer
Dim p As Integer
Dim sumthese()

REFR = 1

bFound = False
For Each c In Colhead.Cells

If Trim(c.Value) = Trim(Product) And _
Trim(Cells((c.Row - 1), (c.Column)).Value) = Trim(Avars) Then
sumthese() = Range(Cells(c.Row + 1, c.Column), Cells(46, c.Column))
bFound = True
Exit for
End If

Next c

If bFound Then
REFR = sumthese
End If

End Function

--
Regards,
Tom Ogilvy


PBcorn said:
I am using a UDF (code at bottom) to look up particular combinations of 2
column headers and return a range from below them. so where Avars = say, A,B,
and Product = say, Q,W,E,R,T,Y,.... (this UDF is then inserted in place of
a particular range argument in existing sumproduct formulae.)

Avars is a string argument in the function

Product is a range (a cell - column header in the column where the formula
is used)

source table:

A A A A A A B B B B B B B
Q W E R T Y Q W E R T Y
[
Data ranges in here

]

The problem is that although the function correctly looks up the relevant
values in the first part of the table (Avars= A), for the second part (Avars
= B), it seems unable to find some combinations, e.g. BQ, BR and BT and
therefore cannot return the ranges below them. The strange thing is that it
finds BW and BE without any problem.
I have tested the lookup code in a sub, it works fine. I have tested all the
arguments in the function.
Please help or suggest an alternative way of doing this.

Thanks

Function REFR(Product As Range, Colhead As Range, Avars As String)

Application.Volatile

Dim c As Range
Dim cs As Integer
Dim p As Integer
Dim sumthese()

p = Colhead.Columns.Count

For Each c In Colhead.Cells

If Trim(c.Value) = Trim(Product) And _
Trim(Cells((c.Row - 1), (c.Column)).Value) = Trim(Avars) Then
sumthese() = Range(Cells(c.Row + 1, c.Column), Cells(46, c.Column))
Else: cs = cs + 1
End If

Next c

If cs = p Then
REFR = 1
Else
REFR = sumthese
End If

End Function
Click to expand...
Click to expand...
Click to expand...
 
P

PBcorn

I also set up simplified copy of the workbook and the UDF worked. I also
flipped the if condition around to reflect the header layout of AAAAA/QWERTY
as you suggested just in case. As for the original workbook, I just can't
figure it out. The UDF just refuses to recognise the equivalent of BQ,BR and
BT.



Tom Ogilvy said:
Since you raised the issue, I set up a workbook as you describe and tested it
for all combinations.

All worked for me.

Based on my reading of your code, your header rows should be

Q W E R T Y Q W E R T Y
A A A A A A B B B B B B B

rather than
as you posted.

anyway, it worked for me - I tested my version, but my version was
consistent with your version as far as the header rows.

--
Regards,
Tom Ogilvy





PBcorn said:
Hi, thanks but this still doesn't work.

Tom Ogilvy said:
Function REFR(Product As Range, Colhead As Range, Avars As String)

Application.Volatile

Dim c As Range
Dim cs As Integer
Dim p As Integer
Dim sumthese()

REFR = 1

bFound = False
For Each c In Colhead.Cells

If Trim(c.Value) = Trim(Product) And _
Trim(Cells((c.Row - 1), (c.Column)).Value) = Trim(Avars) Then
sumthese() = Range(Cells(c.Row + 1, c.Column), Cells(46, c.Column))
bFound = True
Exit for
End If

Next c

If bFound Then
REFR = sumthese
End If

End Function

--
Regards,
Tom Ogilvy


:


I am using a UDF (code at bottom) to look up particular combinations of 2
column headers and return a range from below them. so where Avars = say, A,B,
and Product = say, Q,W,E,R,T,Y,.... (this UDF is then inserted in place of
a particular range argument in existing sumproduct formulae.)

Avars is a string argument in the function

Product is a range (a cell - column header in the column where the formula
is used)

source table:

A A A A A A B B B B B B B
Q W E R T Y Q W E R T Y
[
Data ranges in here

]

The problem is that although the function correctly looks up the relevant
values in the first part of the table (Avars= A), for the second part (Avars
= B), it seems unable to find some combinations, e.g. BQ, BR and BT and
therefore cannot return the ranges below them. The strange thing is that it
finds BW and BE without any problem.
I have tested the lookup code in a sub, it works fine. I have tested all the
arguments in the function.
Please help or suggest an alternative way of doing this.

Thanks

Function REFR(Product As Range, Colhead As Range, Avars As String)

Application.Volatile

Dim c As Range
Dim cs As Integer
Dim p As Integer
Dim sumthese()

p = Colhead.Columns.Count

For Each c In Colhead.Cells

If Trim(c.Value) = Trim(Product) And _
Trim(Cells((c.Row - 1), (c.Column)).Value) = Trim(Avars) Then
sumthese() = Range(Cells(c.Row + 1, c.Column), Cells(46, c.Column))
Else: cs = cs + 1
End If

Next c

If cs = p Then
REFR = 1
Else
REFR = sumthese
End If

End Function
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ogilvy

Sounds like a data problem. Sure there isn't a non-printing character within
the cells as well. Even if it is one character wide, check the value
actually stored in the cell.

--
Regards,
Tom Ogilvy



PBcorn said:
I also set up simplified copy of the workbook and the UDF worked. I also
flipped the if condition around to reflect the header layout of AAAAA/QWERTY
as you suggested just in case. As for the original workbook, I just can't
figure it out. The UDF just refuses to recognise the equivalent of BQ,BR and
BT.



Tom Ogilvy said:
Since you raised the issue, I set up a workbook as you describe and tested it
for all combinations.

All worked for me.

Based on my reading of your code, your header rows should be

Q W E R T Y Q W E R T Y
A A A A A A B B B B B B B

rather than
A A A A A A B B B B B B B
Q W E R T Y Q W E R T Y
Click to expand...
as you posted.

anyway, it worked for me - I tested my version, but my version was
consistent with your version as far as the header rows.

--
Regards,
Tom Ogilvy





PBcorn said:
Hi, thanks but this still doesn't work.

:

Function REFR(Product As Range, Colhead As Range, Avars As String)

Application.Volatile

Dim c As Range
Dim cs As Integer
Dim p As Integer
Dim sumthese()

REFR = 1

bFound = False
For Each c In Colhead.Cells

If Trim(c.Value) = Trim(Product) And _
Trim(Cells((c.Row - 1), (c.Column)).Value) = Trim(Avars) Then
sumthese() = Range(Cells(c.Row + 1, c.Column), Cells(46, c.Column))
bFound = True
Exit for
End If

Next c

If bFound Then
REFR = sumthese
End If

End Function

--
Regards,
Tom Ogilvy


:


I am using a UDF (code at bottom) to look up particular combinations of 2
column headers and return a range from below them. so where Avars = say, A,B,
and Product = say, Q,W,E,R,T,Y,.... (this UDF is then inserted in place of
a particular range argument in existing sumproduct formulae.)

Avars is a string argument in the function

Product is a range (a cell - column header in the column where the formula
is used)

source table:

A A A A A A B B B B B B B
Q W E R T Y Q W E R T Y
[
Data ranges in here

]

The problem is that although the function correctly looks up the relevant
values in the first part of the table (Avars= A), for the second part (Avars
= B), it seems unable to find some combinations, e.g. BQ, BR and BT and
therefore cannot return the ranges below them. The strange thing is that it
finds BW and BE without any problem.
I have tested the lookup code in a sub, it works fine. I have tested all the
arguments in the function.
Please help or suggest an alternative way of doing this.

Thanks

Function REFR(Product As Range, Colhead As Range, Avars As String)

Application.Volatile

Dim c As Range
Dim cs As Integer
Dim p As Integer
Dim sumthese()

p = Colhead.Columns.Count

For Each c In Colhead.Cells

If Trim(c.Value) = Trim(Product) And _
Trim(Cells((c.Row - 1), (c.Column)).Value) = Trim(Avars) Then
sumthese() = Range(Cells(c.Row + 1, c.Column), Cells(46, c.Column))
Else: cs = cs + 1
End If

Next c

If cs = p Then
REFR = 1
Else
REFR = sumthese
End If

End Function
Click to expand...
Click to expand...
Click to expand...
 
P

PBcorn

OK i have used IF formulas to test whether the text strings in the cells are
the same as what is displayed. They are.

My problem now is that i need to find an alternative to the sumproduct
formulas to which REFR was passing the range. I was using the following to
sum figures in the table based on two cols of row labels in cols A and B.

=IF(refr(C47,$A$7:$AR$7,"B")=1,0,SUMPRODUCT(((TRIM($A$8:$A46)="Oranges"))*((TRIM($B$8:$B46)="Small")+(TRIM($B$8:$B46)="Medium")),refr(C47,$A$7:$AR$7,"B")))

so the row labels look like:
col a col b
Oranges Small
Oranges Medium
Oranges Large
Apples Very Small
Apples Small
Apples Medium

"B" in refr is as discussed previously. The sumproduct strings are just for
illustration. The reason i used this approach is because after creating the
sumproduct formulas, i realised that the combinations of column headers in
the data varied. So I wanted a quick fix to add in to the sumproduct formulas
to enable them to pick the correct sum range even if the columns in the data
changed.

Any suggestions?

Thanks

Tom Ogilvy said:
Sounds like a data problem. Sure there isn't a non-printing character within
the cells as well. Even if it is one character wide, check the value
actually stored in the cell.

--
Regards,
Tom Ogilvy



PBcorn said:
I also set up simplified copy of the workbook and the UDF worked. I also
flipped the if condition around to reflect the header layout of AAAAA/QWERTY
as you suggested just in case. As for the original workbook, I just can't
figure it out. The UDF just refuses to recognise the equivalent of BQ,BR and
BT.



Tom Ogilvy said:
Since you raised the issue, I set up a workbook as you describe and tested it
for all combinations.

All worked for me.

Based on my reading of your code, your header rows should be

Q W E R T Y Q W E R T Y
A A A A A A B B B B B B B

rather than

A A A A A A B B B B B B B
Q W E R T Y Q W E R T Y
as you posted.

anyway, it worked for me - I tested my version, but my version was
consistent with your version as far as the header rows.

--
Regards,
Tom Ogilvy





:

Hi, thanks but this still doesn't work.

:

Function REFR(Product As Range, Colhead As Range, Avars As String)

Application.Volatile

Dim c As Range
Dim cs As Integer
Dim p As Integer
Dim sumthese()

REFR = 1

bFound = False
For Each c In Colhead.Cells

If Trim(c.Value) = Trim(Product) And _
Trim(Cells((c.Row - 1), (c.Column)).Value) = Trim(Avars) Then
sumthese() = Range(Cells(c.Row + 1, c.Column), Cells(46, c.Column))
bFound = True
Exit for
End If

Next c

If bFound Then
REFR = sumthese
End If

End Function

--
Regards,
Tom Ogilvy


:


I am using a UDF (code at bottom) to look up particular combinations of 2
column headers and return a range from below them. so where Avars = say, A,B,
and Product = say, Q,W,E,R,T,Y,.... (this UDF is then inserted in place of
a particular range argument in existing sumproduct formulae.)

Avars is a string argument in the function

Product is a range (a cell - column header in the column where the formula
is used)

source table:

A A A A A A B B B B B B B
Q W E R T Y Q W E R T Y
[
Data ranges in here

]

The problem is that although the function correctly looks up the relevant
values in the first part of the table (Avars= A), for the second part (Avars
= B), it seems unable to find some combinations, e.g. BQ, BR and BT and
therefore cannot return the ranges below them. The strange thing is that it
finds BW and BE without any problem.
I have tested the lookup code in a sub, it works fine. I have tested all the
arguments in the function.
Please help or suggest an alternative way of doing this.

Thanks

Function REFR(Product As Range, Colhead As Range, Avars As String)

Application.Volatile

Dim c As Range
Dim cs As Integer
Dim p As Integer
Dim sumthese()

p = Colhead.Columns.Count

For Each c In Colhead.Cells

If Trim(c.Value) = Trim(Product) And _
Trim(Cells((c.Row - 1), (c.Column)).Value) = Trim(Avars) Then
sumthese() = Range(Cells(c.Row + 1, c.Column), Cells(46, c.Column))
Else: cs = cs + 1
End If

Next c

If cs = p Then
REFR = 1
Else
REFR = sumthese
End If

End Function
Click to expand...
Click to expand...
Click to expand...
 
P

PBcorn

SOLVED

As usual, a very simple solution. The function was set to return 1 when it
could not find the required combination of column headers. However, when the
function does find the required combination and the first element of the
array (range below) =1, the function also returns 1. This explains the 0
returned by the full if(ref(sumproduct( function, and the apparent inability
of the fucntion to recognise certain combinations.

Tom - Thanks for your help with tidying up my code.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to stop UDF in Original ref'ng copied range 6
passing array reference to sumproduct formula-??? 1
Loop returns only one value, does not loop & an assignment to columnhow-to problem. 26
Array with many cells from a row but not in order or together 11
Fill array with Values 4
type mismatch error in macro in excel 0
Simple example of a range argument to a UDF? 6
LN in UDF 2

Top