VBA Custom function for lookup

[Sami82]

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.


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

 

[Richard Buttrey]

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]

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]

Hi Harlan

This seemed to work, thank you very much. But i was hoping that I coul
simplify it further by making a custom function, so that it would b
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 t
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)),""

 

[Harlan Grove]

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]

Hi Harlan,

Thank you for your help once again.

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

I think I understand most of what is being done in this code but I a
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!

 

[Dave Peterson]

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.

 

[Harlan Grove]

...
....

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]

Hi All,

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

Thanks.

 

[Dave Peterson]

What happens when you use it?

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