VBA Custom function for lookup

**Sami82** · 09-09-2005, 10:56 AM

Hi All,

I'm really hoping you can help me out here, I'm absolutely stumped

. I'm fairly new to VBA but I have managed to find posts/tutorials on most of my queries - thanks to this site!

I am trying to create a custom function in VB to replace a 7 level lookup. I want the function to go something like this:

=ACNLookup(State,product,date)

I currently have the following excel formula in place (beware its extremely messy).

=IF($C7="NSW",OFFSET('ACN-NSW'!$A$3,MATCH(Claim!$B7,'ACN-NSW'!$A$4:$A$30,0),MATCH(Claim!D$6,'ACN-NSW'!$B$3:$IV$3,0)),IF($C7="QLD",OFFSET('ACN-QLD'!$A$3,MATCH(Claim!$B7,'ACN-QLD'!$A$4:$A$30,0),MATCH(Claim!D$6,'ACN-QLD'!$B$3:$IV$3,0)),IF($C7="VIC",OFFSET('ACN-VIC'!$A$3,MATCH(Claim!$B7,'ACN-VIC'!$A$4:$A$30,0),MATCH(Claim!D$6,'ACN-VIC'!$B$3:$IV$3,0)),IF($C7="SA",OFFSET('ACN-SA'!$A$3,MATCH(Claim!$B7,'ACN-SA'!$A$4:$A$30,0),MATCH(Claim!D$6,'ACN-SA'!$B$3:$IV$3,0)),IF($C7="TAS",OFFSET('ACN-TAS'!$A$3,MATCH(Claim!$B7,'ACN-TAS'!$A$4:$A$30,0),MATCH(Claim!D$6,'ACN-TAS'!$B$3:$IV$3,0)),IF($C7="WA",OFFSET('ACN-WA'!$A$3,MATCH(Claim!$B7,'ACN-WA'!$A$4:$A$30,0),MATCH(Claim!D$6,'ACN-WA'!$B$3:$IV$3,0)),""))))))

As you can imagine this gets fairly annoying when copying into separate cells, and i want the ACNLookup function formula (in excel) to appear easy for basic users to edit the referencing cells.

If you could help me out because I'm clueless

Thank you soooo much!

P.S: I have included an example of the spreadsheet to emulate the actual sheet with the data.

Richard Buttrey · 09-09-2005, 12:05 PM

On Fri, 9 Sep 2005 09:56:43 -0500, Sami82
<[email protected]> wrote:

>
>Hi All,
>
>I'm really hoping you can help me out here, I'm absolutely stumped
>

. I'm fairly new to VBA but I have managed to find
>posts/tutorials on most of my queries - thanks to this site!
>
>I am trying to create a custom function in VB to replace a 7 level
>lookup. I want the function to go something like this:
>
>=ACNLookup(State,product,date)

[snipped]

>+-------------------------------------------------------------------+
>|Filename: ACNLOOKUP().zip |
>|Download: http://www.excelforum.com/attachment.php?postid=3796 |
>+-------------------------------------------------------------------+

That link didn't result in a dowloadable file.

If you'd like to email it directly to me I'll take a look

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Harlan Grove · 09-09-2005, 06:05 PM

Sami82 wrote...
....
>I am trying to create a custom function in VB to replace a 7 level
>lookup. I want the function to go something like this:
>
>=ACNLookup(State,product,date)
>
>I currently have the following excel formula in place (beware its
>extremely messy).
>
>=IF($C7="NSW",OFFSET('ACN-NSW'!$A$3,
>MATCH(Claim!$B7,'ACN-NSW'!$A$4:$A$30,0),
>MATCH(Claim!D$6,'ACN-NSW'!$B$3:$IV$3,0)),
>IF($C7="QLD",OFFSET('ACN-QLD'!$A$3,
>MATCH(Claim!$B7,'ACN-QLD'!$A$4:$A$30,0),
>MATCH(Claim!D$6,'ACN-QLD'!$B$3:$IV$3,0)),
>IF($C7="VIC",OFFSET('ACN-VIC'!$A$3,
>MATCH(Claim!$B7,'ACN-VIC'!$A$4:$A$30,0),
>MATCH(Claim!D$6,'ACN-VIC'!$B$3:$IV$3,0)),
>IF($C7="SA",OFFSET('ACN-SA'!$A$3,
>MATCH(Claim!$B7,'ACN-SA'!$A$4:$A$30,0),
>MATCH(Claim!D$6,'ACN-SA'!$B$3:$IV$3,0)),
>IF($C7="TAS",OFFSET('ACN-TAS'!$A$3,
>MATCH(Claim!$B7,'ACN-TAS'!$A$4:$A$30,0),
>MATCH(Claim!D$6,'ACN-TAS'!$B$3:$IV$3,0)),
>IF($C7="WA",OFFSET('ACN-WA'!$A$3,
>MATCH(Claim!$B7,'ACN-WA'!$A$4:$A$30,0),
>MATCH(Claim!D$6,'ACN-WA'!$B$3:$IV$3,0)),
>""))))))
....

Looks like you could replace this with

=IF(OR($C7={"NSW","QLD","VIC","SA","TAS","WA"}),
OFFSET(INDIRECT("'ACN-"&$C7&"'!$A$3"),
MATCH(Claim!$B7,INDIRECT("'ACN-"&$C7&"'!$A$4:$A$30"),0),
MATCH(Claim!D$6,INDIRECT("'ACN-"&$C7&"'!$B$3:$IV$3"),0)),"")

**Sami82** · 09-09-2005, 08:06 PM

Hi Harlan

This seemed to work, thank you very much. But i was hoping that I could simplify it further by making a custom function, so that it would be easy to explain to basic users, it would look something like this:

=ACNLookup(State,product,date)

I just dont know how to turn the formula below into the VB code.

Thank you.

--> Richard Buttrey: I can't actually find your email address to forward it to you.

....

Looks like you could replace this with

=IF(OR($C7={"NSW","QLD","VIC","SA","TAS","WA"}),
OFFSET(INDIRECT("'ACN-"&$C7&"'!$A$3"),
MATCH(Claim!$B7,INDIRECT("'ACN-"&$C7&"'!$A$4:$A$30"),0),
MATCH(Claim!D$6,INDIRECT("'ACN-"&$C7&"'!$B$3:$IV$3"),0)),"")[/QUOTE]

Harlan Grove · 09-09-2005, 11:05 PM

"Sami82" wrote...
>This seemed to work, thank you very much. But i was hoping that I
>could simplify it further by making a custom function, so that it
>would be easy to explain to basic users, it would look something
>like this:
>
>=ACNLookup(State,product,date)
....

Without minimal error checking, something like

Function ACNLookup( _
s As string, _
p As String, _
d As Variant _
) As Variant
'----------------------
Dim ws As Worksheet, r As Long, c As Long

On Error Resume Next

Set ws = Application.Caller.Parent.Parent.Worksheets("ACN-" & s)

If Err.Number <> 0 Then
ACNLookup = CVErr(xlErrRef) 'bad worksheet, return #REF!
Exit Function
End If

r = Application.Worksheet.Match(p, ws.Range("A4:A30"), 0)
c = Application.Worksheet.Match(p, ws.Range("B3:IV3"), 0)

If Err.Number <> 0 Then
ACNLookup = CVErr(xlErrNA) 'bad prod/date, return #N/A
Exit Function
End If

ACNLookup = ws.Range("A3").Offset(r, c).Value
End Function

**Sami82** · 09-11-2005, 01:30 AM

Hi Harlan,

Thank you for your help once again.

I'm put this in as a module (function) in excel, but it doesnt seem to want to work for me. If there is nothing in the state cell reference i get #REF, which is fine, but as soon as I put any of the correct references in I keep getting #NA. Is there something I should be editing to make this work.

I think I understand most of what is being done in this code but I am stumped on the following line:

Set ws = Application.Caller.Parent.Parent.Worksheets("ACN-" & s)

Also I can't work out how it references the date?

Thank you again!

Originally Posted by Harlan Grove

"Sami82" wrote...
>This seemed to work, thank you very much. But i was hoping that I
>could simplify it further by making a custom function, so that it
>would be easy to explain to basic users, it would look something
>like this:
>
>=ACNLookup(State,product,date)
....

Without minimal error checking, something like

Function ACNLookup( _
s As string, _
p As String, _
d As Variant _
) As Variant
'----------------------
Dim ws As Worksheet, r As Long, c As Long

On Error Resume Next

Set ws = Application.Caller.Parent.Parent.Worksheets("ACN-" & s)

If Err.Number <> 0 Then
ACNLookup = CVErr(xlErrRef) 'bad worksheet, return #REF!
Exit Function
End If

r = Application.Worksheet.Match(p, ws.Range("A4:A30"), 0)
c = Application.Worksheet.Match(p, ws.Range("B3:IV3"), 0)

If Err.Number <> 0 Then
ACNLookup = CVErr(xlErrNA) 'bad prod/date, return #N/A
Exit Function
End If

ACNLookup = ws.Range("A3").Offset(r, c).Value
End Function

Dave Peterson · 09-11-2005, 10:05 AM

The easy part first:

Set ws = Application.Caller.Parent.Parent.Worksheets("ACN-" & s)

Application.caller is the cell that holds the formula

so application.caller.parent is the worksheet that holds that cell that holds
the formula

so appliacation.caller.parent.parent is the workbook that holds that worksheet
that holds the cell that holds the formula

So you're finding the correct workbook and then using a worksheet named: ACN-
(and whatever you pass as S)

I didn't open your workbook (or look at other posts in the thread...),

But you're passing the date (as d) in:

Function ACNLookup( _
s As string, _
p As String, _
d As Variant _
) As Variant

I think one of these should be using p and one should be using d:

r = Application.Worksheet.Match(p, ws.Range("A4:A30"), 0)
c = Application.Worksheet.Match(p, ws.Range("B3:IV3"), 0)

If the dates are in row 3, then use D in the second line. If the dates are in
column A, then use it in the first line.

Sami82 wrote:
>
> Hi Harlan,
>
> Thank you for your help once again.
>
> I'm put this in as a module (function) in excel, but it doesnt seem to
> want to work for me. If there is nothing in the state cell reference i
> get #REF, which is fine, but as soon as I put any of the correct
> references in I keep getting #NA. Is there something I should be
> editing to make this work.
>
> I think I understand most of what is being done in this code but I am
> stumped on the following line:
>
> Set ws = Application.Caller.Parent.Parent.Worksheets("ACN-" & s)
>
> Also I can't work out how it references the date?
>
> Thank you again!
>
> Harlan Grove Wrote:
> > "Sami82" wrote...
> > >This seemed to work, thank you very much. But i was hoping that I
> > >could simplify it further by making a custom function, so that it
> > >would be easy to explain to basic users, it would look something
> > >like this:
> > >
> > >=ACNLookup(State,product,date)
> > ....
> >
> > Without minimal error checking, something like
> >
> >
> > Function ACNLookup( _
> > s As string, _
> > p As String, _
> > d As Variant _
> > ) As Variant
> > '----------------------
> > Dim ws As Worksheet, r As Long, c As Long
> >
> > On Error Resume Next
> >
> > Set ws = Application.Caller.Parent.Parent.Worksheets("ACN-" & s)
> >
> > If Err.Number <> 0 Then
> > ACNLookup = CVErr(xlErrRef) 'bad worksheet, return #REF!
> > Exit Function
> > End If
> >
> > r = Application.Worksheet.Match(p, ws.Range("A4:A30"), 0)
> > c = Application.Worksheet.Match(p, ws.Range("B3:IV3"), 0)
> >
> > If Err.Number <> 0 Then
> > ACNLookup = CVErr(xlErrNA) 'bad prod/date, return #N/A
> > Exit Function
> > End If
> >
> > ACNLookup = ws.Range("A3").Offset(r, c).Value
> > End Function
>
> --
> Sami82
> ------------------------------------------------------------------------
> Sami82's Profile: http://www.excelforum.com/member.php...o&userid=27111
> View this thread: http://www.excelforum.com/showthread...hreadid=466252

--

Dave Peterson

Harlan Grove · 09-11-2005, 10:05 PM

"Dave Peterson" wrote...
....
>But you're passing the date (as d) in:
>
>Function ACNLookup( _
>s As string, _
>p As String, _
>d As Variant _
>) As Variant
>
>I think one of these should be using p and one should be using d:
>
> r = Application.Worksheet.Match(p, ws.Range("A4:A30"), 0)
> c = Application.Worksheet.Match(p, ws.Range("B3:IV3"), 0)
>
>If the dates are in row 3, then use D in the second line. If the
>dates are in column A, then use it in the first line.
....

Yup, I should have written

c = Application.Worksheet.Match(d, ws.Range("B3:IV3"), 0)

**Sami82** · 09-12-2005, 04:18 AM

Hi All,

Made the change to the function, but for some reason it still isnt working. Any more suggestions?

Thanks.

Dave Peterson · 09-12-2005, 11:05 AM

What happens when you use it?

Do you get a #ref! error or what???

Sami82 wrote:
>
> Hi All,
>
> Made the change to the function, but for some reason it still isnt
> working. Any more suggestions?
>
> Thanks.
>
> --
> Sami82
> ------------------------------------------------------------------------
> Sami82's Profile: http://www.excelforum.com/member.php...o&userid=27111
> View this thread: http://www.excelforum.com/showthread...hreadid=466252

--

Dave Peterson