Use a VBA function to return a factor
 

I'm developing a template program to allocate income amounts to 4 different
classes based upon prorate factors that can vary by category of income and
effective date of the prorate factors. The table of factors is on the same
sheet as the list (database) of income amounts to be allocated.

Here is a sample of prorate factors:
Category Date HSP CP
EpicInc 8/31/2001 0.49 0.51
InterestInc 8/31/2001 1
InterestInc 10/1/2002 0.6 0.4
RentInc 8/31/2001 1
RentInc 9/30/2002 0.56 0.50
RentInc 1/31/2004 1

Here is the Function proceedu

Function DepLookup(Cat As Range, TDate As Range)
' Looks up allocation based upon Category and date. IncTable is a named range
' on the worksheet containing the categories, effective dates and prorate
percentages.

Dim rng As Range
Dim workrange As Range, CatRows As Integer

Sheets("Database").Select
Set rng = Sheets("database").Range("IncTable")
rng.AutoFilter
rng.AutoFilter Field:=1, Criteria1:=Cat


'the following statement removes the first or header row
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
Set rng = rng.SpecialCells(xlVisible)


If rng.Rows.Count = 0 Then
DepLookup = 0
End If
If rng.Rows.Count = 1 Then
DepLookup = rng.Range("C1").Value
End If
If rng.Rows.Count 1 Then
CatRows = rng.Rows.Count
Set workrange = rng.Range(Cells(1, 2), Cells(CatRows, 3))
DepLookup = Application.WorksheetFunction.Lookup(TDate, workrange)
End If

End Function

It works for the RentInc line item, but not the other categories. The
troubling thing is the effective date of a line of prorate factors. Prorate
factors are effective on and after the effective date and are superceded by a
line item with a later effective date. The data has dates back through
several years.

A sub proceedure with this same code works. I want a function procedure so
the results times the amount to be allocated will be entered in the cell with
the Function Proceedure.

Much of the code I borrowed from other entries/answers on this forum and I
appreciate the help a bunch. I've not found another that addresses this
problem specifically.

Any help will be appreciated.

Use a VBA function to return a factor
 

I didn't read all of your code. But I did see that you´re trying to change
things in worksheets from within a Function.
That is not possible.
A function, if called from a worksheet, can only return a value to replace
its call.
It cannot change anything in a workbook. That is, if the function was called
from a worksheet. Directly or indirectly.
Any attempt to change things via calls to subs in a function will fail; the
idea is that a function can only return a vale, not do anything else.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"BootStrapSteve" wrote in message
...
I'm developing a template program to allocate income amounts to 4
different
classes based upon prorate factors that can vary by category of income and
effective date of the prorate factors. The table of factors is on the same
sheet as the list (database) of income amounts to be allocated.

Here is a sample of prorate factors:
Category Date HSP CP
EpicInc 8/31/2001 0.49 0.51
InterestInc 8/31/2001 1
InterestInc 10/1/2002 0.6 0.4
RentInc 8/31/2001 1
RentInc 9/30/2002 0.56 0.50
RentInc 1/31/2004 1

Here is the Function proceedu

Function DepLookup(Cat As Range, TDate As Range)
' Looks up allocation based upon Category and date. IncTable is a named
range
' on the worksheet containing the categories, effective dates and
prorate
percentages.

Dim rng As Range
Dim workrange As Range, CatRows As Integer

Sheets("Database").Select
Set rng = Sheets("database").Range("IncTable")
rng.AutoFilter
rng.AutoFilter Field:=1, Criteria1:=Cat


'the following statement removes the first or header row
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
Set rng = rng.SpecialCells(xlVisible)


If rng.Rows.Count = 0 Then
DepLookup = 0
End If
If rng.Rows.Count = 1 Then
DepLookup = rng.Range("C1").Value
End If
If rng.Rows.Count 1 Then
CatRows = rng.Rows.Count
Set workrange = rng.Range(Cells(1, 2), Cells(CatRows, 3))
DepLookup = Application.WorksheetFunction.Lookup(TDate,
workrange)
End If

End Function

It works for the RentInc line item, but not the other categories. The
troubling thing is the effective date of a line of prorate factors.
Prorate
factors are effective on and after the effective date and are superceded
by a
line item with a later effective date. The data has dates back through
several years.

A sub proceedure with this same code works. I want a function procedure
so
the results times the amount to be allocated will be entered in the cell
with
the Function Proceedure.

Much of the code I borrowed from other entries/answers on this forum and I
appreciate the help a bunch. I've not found another that addresses this
problem specifically.

Any help will be appreciated.

Use a VBA function to return a factor
 

OK. That answers the function question. Is there a way to lookup an amount
in a table to use to multiply times an input cell. I want the function to
return a number that is looked up in a table.
Thanks,

--
BootStrapSteve


"Niek Otten" wrote:

I didn't read all of your code. But I did see that you´re trying to change
things in worksheets from within a Function.
That is not possible.
A function, if called from a worksheet, can only return a value to replace
its call.
It cannot change anything in a workbook. That is, if the function was called
from a worksheet. Directly or indirectly.
Any attempt to change things via calls to subs in a function will fail; the
idea is that a function can only return a vale, not do anything else.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"BootStrapSteve" wrote in message
...
I'm developing a template program to allocate income amounts to 4
different
classes based upon prorate factors that can vary by category of income and
effective date of the prorate factors. The table of factors is on the same
sheet as the list (database) of income amounts to be allocated.

Here is a sample of prorate factors:
Category Date HSP CP
EpicInc 8/31/2001 0.49 0.51
InterestInc 8/31/2001 1
InterestInc 10/1/2002 0.6 0.4
RentInc 8/31/2001 1
RentInc 9/30/2002 0.56 0.50
RentInc 1/31/2004 1

Here is the Function proceedu

Function DepLookup(Cat As Range, TDate As Range)
' Looks up allocation based upon Category and date. IncTable is a named
range
' on the worksheet containing the categories, effective dates and
prorate
percentages.

Dim rng As Range
Dim workrange As Range, CatRows As Integer

Sheets("Database").Select
Set rng = Sheets("database").Range("IncTable")
rng.AutoFilter
rng.AutoFilter Field:=1, Criteria1:=Cat


'the following statement removes the first or header row
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
Set rng = rng.SpecialCells(xlVisible)


If rng.Rows.Count = 0 Then
DepLookup = 0
End If
If rng.Rows.Count = 1 Then
DepLookup = rng.Range("C1").Value
End If
If rng.Rows.Count 1 Then
CatRows = rng.Rows.Count
Set workrange = rng.Range(Cells(1, 2), Cells(CatRows, 3))
DepLookup = Application.WorksheetFunction.Lookup(TDate,
workrange)
End If

End Function

It works for the RentInc line item, but not the other categories. The
troubling thing is the effective date of a line of prorate factors.
Prorate
factors are effective on and after the effective date and are superceded
by a
line item with a later effective date. The data has dates back through
several years.

A sub proceedure with this same code works. I want a function procedure
so
the results times the amount to be allocated will be entered in the cell
with
the Function Proceedure.

Much of the code I borrowed from other entries/answers on this forum and I
appreciate the help a bunch. I've not found another that addresses this
problem specifically.

Any help will be appreciated.