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 |
+-------------------------------------------------------------------+

--
Sami82
------------------------------------------------------------------------
Sami82's Profile: http://www.excelforum.com/member.php...o&userid=27111
View this thread: http://www.excelforum.com/showthread...hreadid=466252

On Fri, 9 Sep 2005 09:56:43 -0500, Sami82
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
__________________________

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)),"")

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)),"")


--
Sami82
------------------------------------------------------------------------
Sami82's Profile: http://www.excelforum.com/member.php...o&userid=27111
View this thread: http://www.excelforum.com/showthread...hreadid=466252

"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

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

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

"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)

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

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