user defined function
 

I am attempting to define a function that looks up a value on another
worksheet and returns that value to the active cell. Sample data is below
and the spreadsheet is attached.

Basically I want enter the meals on sheet 1 by looking up the value of
sheet1.week-day on sheet 2 and then return the entree for that value.
Currently I am using =VLOOKUP(G4,Menu,4,0). 'Menu' is the range on sheet 2.
The problem is there are 20,0000 rows on sheet 1 and the workbook is getting
large - actually there is another part of the workbook that is causing the
size problem but fixing this will help me fix the other. My attempt at the
user-defined function is listed below. It returns #Ref right now. Any
guidance would be appreciated.

R/

Brian

Sheet 1 Sheet 2
Meal Week-Day Week-Day Week # Day Entrée
Week 1-Monday Week 1-Monday Week 1 Monday Hamburgers
Week 2-Friday Week 1-Tuesday Week 1 Tuesday Hot Dogs
Week 2-Tuesday Week 1-Wednesday Week 1 Wednesday Pizza
Week 1-Thursday Week 1-Thursday Week 1 Thursday Chicken
Week 2-Thursday Week 1-Friday Week 1 Friday Stew
Week 2-Monday Week 2-Monday Week 2 Monday Pork Chops
Week 2-Wednesday Week 2-Tuesday Week 2 Tuesday Roast
Week 2-Wednesday Week 2 Wednesday Hamburgers
Week 2-Thursday Week 2 Thursday Hot Dogs
Week 2-Friday Week 2 Friday Chicken



Function last_done_date()
'returns last_done_date
VLookup = Application.VLookup
Offset = Application.Offset
last_done = VLookup(Offset(ActiveCell, 0, 1, 1, 1), Menu, 4, 0)

End Function

Lots of syntax problems. Try something like (untested):

Function last_done_date() As Date

'returns last_done_date

last_done_date = WorksheetFunction.VLookup _
(ActiveCell.Offset(-1), Range("Menu"), 4, False)

End Function

--

Vasant


"Brian Rogge" <brian -at- minnplace dot com wrote in message
. ..
I am attempting to define a function that looks up a value on another
worksheet and returns that value to the active cell. Sample data is below
and the spreadsheet is attached.

Basically I want enter the meals on sheet 1 by looking up the value of
sheet1.week-day on sheet 2 and then return the entree for that value.
Currently I am using =VLOOKUP(G4,Menu,4,0). 'Menu' is the range on sheet
2.
The problem is there are 20,0000 rows on sheet 1 and the workbook is
getting
large - actually there is another part of the workbook that is causing the
size problem but fixing this will help me fix the other. My attempt at
the
user-defined function is listed below. It returns #Ref right now. Any
guidance would be appreciated.

R/

Brian

Sheet 1 Sheet 2
Meal Week-Day Week-Day Week # Day Entrée
Week 1-Monday Week 1-Monday Week 1 Monday Hamburgers
Week 2-Friday Week 1-Tuesday Week 1 Tuesday Hot Dogs
Week 2-Tuesday Week 1-Wednesday Week 1 Wednesday Pizza
Week 1-Thursday Week 1-Thursday Week 1 Thursday Chicken
Week 2-Thursday Week 1-Friday Week 1 Friday Stew
Week 2-Monday Week 2-Monday Week 2 Monday Pork Chops
Week 2-Wednesday Week 2-Tuesday Week 2 Tuesday Roast
Week 2-Wednesday Week 2 Wednesday Hamburgers
Week 2-Thursday Week 2 Thursday Hot Dogs
Week 2-Friday Week 2 Friday Chicken



Function last_done_date()
'returns last_done_date
VLookup = Application.VLookup
Offset = Application.Offset
last_done = VLookup(Offset(ActiveCell, 0, 1, 1, 1), Menu, 4, 0)

End Function

Vasant - that seems to be on the right track. I still can't get the cell
reference to work. But if I hard code the value then it works:

Function last_done_date()

'returns last_done_date

last_done_date = Application.VLookup("Week 1-Monday", Range("Menu"), 4,
False)

End Function

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
Lots of syntax problems. Try something like (untested):

Function last_done_date() As Date

'returns last_done_date

last_done_date = WorksheetFunction.VLookup _
(ActiveCell.Offset(-1), Range("Menu"), 4, False)

End Function

--

Vasant


"Brian Rogge" <brian -at- minnplace dot com wrote in message
. ..
I am attempting to define a function that looks up a value on another
worksheet and returns that value to the active cell. Sample data is
below
and the spreadsheet is attached.

Basically I want enter the meals on sheet 1 by looking up the value of
sheet1.week-day on sheet 2 and then return the entree for that value.
Currently I am using =VLOOKUP(G4,Menu,4,0). 'Menu' is the range on sheet
2.
The problem is there are 20,0000 rows on sheet 1 and the workbook is
getting
large - actually there is another part of the workbook that is causing
the
size problem but fixing this will help me fix the other. My attempt at
the
user-defined function is listed below. It returns #Ref right now. Any
guidance would be appreciated.

R/

Brian

Sheet 1 Sheet 2
Meal Week-Day Week-Day Week # Day Entrée
Week 1-Monday Week 1-Monday Week 1 Monday Hamburgers
Week 2-Friday Week 1-Tuesday Week 1 Tuesday Hot Dogs
Week 2-Tuesday Week 1-Wednesday Week 1 Wednesday Pizza
Week 1-Thursday Week 1-Thursday Week 1 Thursday Chicken
Week 2-Thursday Week 1-Friday Week 1 Friday Stew
Week 2-Monday Week 2-Monday Week 2 Monday Pork Chops
Week 2-Wednesday Week 2-Tuesday Week 2 Tuesday Roast
Week 2-Wednesday Week 2 Wednesday Hamburgers
Week 2-Thursday Week 2 Thursday Hot Dogs
Week 2-Friday Week 2 Friday Chicken



Function last_done_date()
'returns last_done_date
VLookup = Application.VLookup
Offset = Application.Offset
last_done = VLookup(Offset(ActiveCell, 0, 1, 1, 1), Menu, 4, 0)

End Function

ok, got the offset problem working, sort of. Now i have:

Function last_done_date()
'returns last_done_date
last_done_date = Application.VLookup(ActiveCell.Offset(0, 1), Range("Menu"),
4, False)

End Function

This looks up the correct value. The problem is it returns the value for
the active cell for all cells. In this case the active cell is A2 in teh
table below. So it returns the correct data to all of the cells. It's
working right but not the way I want it to. :-(

Thanks for your help anyhow.

Meal Day
Hamburgers Week 1-Monday
Hamburgers Week 2-Friday
Hamburgers Week 2-Tuesday
Hamburgers Week 1-Thursday
Hamburgers Week 2-Thursday
Hamburgers Week 2-Monday
Hamburgers Week 2-Wednesday





"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
Lots of syntax problems. Try something like (untested):

Function last_done_date() As Date

'returns last_done_date

last_done_date = WorksheetFunction.VLookup _
(ActiveCell.Offset(-1), Range("Menu"), 4, False)

End Function

--

Vasant


"Brian Rogge" <brian -at- minnplace dot com wrote in message
. ..
I am attempting to define a function that looks up a value on another
worksheet and returns that value to the active cell. Sample data is
below
and the spreadsheet is attached.

Basically I want enter the meals on sheet 1 by looking up the value of
sheet1.week-day on sheet 2 and then return the entree for that value.
Currently I am using =VLOOKUP(G4,Menu,4,0). 'Menu' is the range on sheet
2.
The problem is there are 20,0000 rows on sheet 1 and the workbook is
getting
large - actually there is another part of the workbook that is causing
the
size problem but fixing this will help me fix the other. My attempt at
the
user-defined function is listed below. It returns #Ref right now. Any
guidance would be appreciated.

R/

Brian

Sheet 1 Sheet 2
Meal Week-Day Week-Day Week # Day Entrée
Week 1-Monday Week 1-Monday Week 1 Monday Hamburgers
Week 2-Friday Week 1-Tuesday Week 1 Tuesday Hot Dogs
Week 2-Tuesday Week 1-Wednesday Week 1 Wednesday Pizza
Week 1-Thursday Week 1-Thursday Week 1 Thursday Chicken
Week 2-Thursday Week 1-Friday Week 1 Friday Stew
Week 2-Monday Week 2-Monday Week 2 Monday Pork Chops
Week 2-Wednesday Week 2-Tuesday Week 2 Tuesday Roast
Week 2-Wednesday Week 2 Wednesday Hamburgers
Week 2-Thursday Week 2 Thursday Hot Dogs
Week 2-Friday Week 2 Friday Chicken



Function last_done_date()
'returns last_done_date
VLookup = Application.VLookup
Offset = Application.Offset
last_done = VLookup(Offset(ActiveCell, 0, 1, 1, 1), Menu, 4, 0)

End Function

Vasant - i got it figured out. Thanks for pointing me in the right
direction!

Function last_done_date(week_day)
'returns last_done_date
last_done_date = Application.VLookup(week_day, Range("Menu"), 4, False)
End Function


"Brian Rogge" <brian -at- minnplace dot com wrote in message
. ..
I am attempting to define a function that looks up a value on another
worksheet and returns that value to the active cell. Sample data is below
and the spreadsheet is attached.

Basically I want enter the meals on sheet 1 by looking up the value of
sheet1.week-day on sheet 2 and then return the entree for that value.
Currently I am using =VLOOKUP(G4,Menu,4,0). 'Menu' is the range on sheet
2. The problem is there are 20,0000 rows on sheet 1 and the workbook is
getting large - actually there is another part of the workbook that is
causing the size problem but fixing this will help me fix the other. My
attempt at the user-defined function is listed below. It returns #Ref
right now. Any guidance would be appreciated.

R/

Brian

Sheet 1 Sheet 2
Meal Week-Day Week-Day Week # Day Entrée
Week 1-Monday Week 1-Monday Week 1 Monday Hamburgers
Week 2-Friday Week 1-Tuesday Week 1 Tuesday Hot Dogs
Week 2-Tuesday Week 1-Wednesday Week 1 Wednesday Pizza
Week 1-Thursday Week 1-Thursday Week 1 Thursday Chicken
Week 2-Thursday Week 1-Friday Week 1 Friday Stew
Week 2-Monday Week 2-Monday Week 2 Monday Pork Chops
Week 2-Wednesday Week 2-Tuesday Week 2 Tuesday Roast
Week 2-Wednesday Week 2 Wednesday Hamburgers
Week 2-Thursday Week 2 Thursday Hot Dogs
Week 2-Friday Week 2 Friday Chicken



Function last_done_date()
'returns last_done_date
VLookup = Application.VLookup
Offset = Application.Offset
last_done = VLookup(Offset(ActiveCell, 0, 1, 1, 1), Menu, 4, 0)

End Function

Good; glad to hear it! :-)

--

Vasant

"Brian Rogge" <brian -at- minnplace dot com wrote in message
. ..
Vasant - i got it figured out. Thanks for pointing me in the right
direction!

Function last_done_date(week_day)
'returns last_done_date
last_done_date = Application.VLookup(week_day, Range("Menu"), 4, False)
End Function


"Brian Rogge" <brian -at- minnplace dot com wrote in message
. ..
I am attempting to define a function that looks up a value on another
worksheet and returns that value to the active cell. Sample data is
below
and the spreadsheet is attached.

Basically I want enter the meals on sheet 1 by looking up the value of
sheet1.week-day on sheet 2 and then return the entree for that value.
Currently I am using =VLOOKUP(G4,Menu,4,0). 'Menu' is the range on
sheet
2. The problem is there are 20,0000 rows on sheet 1 and the workbook is
getting large - actually there is another part of the workbook that is
causing the size problem but fixing this will help me fix the other. My
attempt at the user-defined function is listed below. It returns #Ref
right now. Any guidance would be appreciated.

R/

Brian

Sheet 1 Sheet 2
Meal Week-Day Week-Day Week # Day Entrée
Week 1-Monday Week 1-Monday Week 1 Monday Hamburgers
Week 2-Friday Week 1-Tuesday Week 1 Tuesday Hot Dogs
Week 2-Tuesday Week 1-Wednesday Week 1 Wednesday Pizza
Week 1-Thursday Week 1-Thursday Week 1 Thursday Chicken
Week 2-Thursday Week 1-Friday Week 1 Friday Stew
Week 2-Monday Week 2-Monday Week 2 Monday Pork Chops
Week 2-Wednesday Week 2-Tuesday Week 2 Tuesday Roast
Week 2-Wednesday Week 2 Wednesday Hamburgers
Week 2-Thursday Week 2 Thursday Hot Dogs
Week 2-Friday Week 2 Friday Chicken



Function last_done_date()
'returns last_done_date
VLookup = Application.VLookup
Offset = Application.Offset
last_done = VLookup(Offset(ActiveCell, 0, 1, 1, 1), Menu, 4, 0)

End Function