Good evening all,
After a full week of searching and trying on my own I give up!
I know what I want but I can't write it, it's very frustrating!!

If anyone has some time to help me it would be appreciated.

Below is my commission model. I want to be able to add an if then
statement to it. The catch is, I want the if then statement to look at
an offset cell besides the active function cell and if it has a number
(discount) in it then apply, if not carry on as normal.

Public Tier1 As Double
Public Tier2 As Double
Public Tier3 As Double
Public Tier4 As Double
Public Tier5 As Double

Sub SetTier()
Tier1 = Worksheets("Fee schedule").Range("D3").Value
Tier2 = Worksheets("Fee schedule").Range("E3").Value
Tier3 = Worksheets("Fee schedule").Range("F3").Value
Tier4 = Worksheets("Fee schedule").Range("G3").Value
Tier5 = Worksheets("Fee schedule").Range("H3").Value
End Sub

Function fee(Assets)
' calculates annual management fee
SetTier
Select Case Assets
Case 1 To 499999.99
fee = Assets * Tier1
Case 500000 To 999999.99
fee = 500000 * Tier1 + (Assets - 500000) * Tier2
Case 1000000 To 1999999.99
fee = 500000 * Tier1 + 500000 * Tier2 + (Assets - 1000000) *
Tier3
Case 2000000 To 5000000
fee = 500000 * Tier1 + 500000 * Tier2 + 1000000 * Tier3 + _
(Assets - 2000000) * Tier4
Case Is = 5000000
fee = 500000 * Tier1 + 500000 * Tier2 + 1000000 * Tier3 + _
3000000 * Tier4 + (Assets - 5000000) * Tier5
End Select
End Function

The if statement will allow me to have a variable fee since I have more
than one client and they can all have different fee's.

If I have not made myself clear or if you think there is a better way of
doing this please say so.

Thanks again.

RTB


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Robert,

The easist thing I can think of to save you using the 'if then' statement is
just simply alter your formula to deal with the discount.

Just add 1*(1-x) to deal with the discount

where x is the discount.

David,
thanks for the reply. However, the discount can be different for each
client, so i don't think hard coding will work. do you think what i'm
requesting is even possible with VBA?
thanks.
Robert



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Robert yep it's possible.

I have to do a bit of guessing here since I can't see your model. Or I have
totaly misinterpreted what you said (very likely in my case)

Col A Col B
1 NAME Discount
2 Fred 20%

I take it you are processing the data for each indivudal

So something like (note I haven't tested this)

Sub Define_Data_To USe ()
Dim Name as String
Dim Dis as double
Dim i as integer

'asuming you have 100 clients to run the analysis on
For i = 1 to 100

'to do simple offsetting to get the data
With Worksheets("Data Set")
Set Name = .cells (2,i)
'to change the offset change the 1 to the appropraite column or row
number -1, as i is already 1)
Set Dis = .cells(2,1+i)
End With

'Once this is defined then simply pass the data you wish to use to the
function)

Function fee(Assets As "please define this as it will save you in the end",
Name As String, Dis As Double)

'then rewite your function to include the discount rate

'then do what you want with the results.

'this means that each time you run the model you are defining the name and
the discount rate to use for that individual

You could also use a Vlookup function to set the Name and Dis variables by
just changing the cloum number to return the data


Any questions let me know

David



"robert burger" wrote in message
...
David,
thanks for the reply. However, the discount can be different for each
client, so i don't think hard coding will work. do you think what i'm
requesting is even possible with VBA?
thanks.
Robert



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

David,
Thanks for taking the time to help. David, i'm new at VBA and am having
trouble understanding what your saying? I kinda get the first part but
you lose me on:

'Once this is defined then simply pass the data you wish to use to the
function)

Function fee(Assets As "please define this as it will save you in the
end",
Name As String, Dis As Double)

'then rewite your function to include the discount rate

'then do what you want with the results.

'this means that each time you run the model you are defining the name
and
the discount rate to use for that individual

fyi posted my fee model in the original post.
Thanks,
Robert



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Robert

VBA is very subjective on how you do things. So yes you do get confused
looking at other peoples suggestions.
I would try something like this but there may be better suggestions out
there.

--------------------

Sub Name()
'Dim all your variables

'set what data you wish to look at
With Worksheets("Data Set")
'range (3a) = name 'after loop is will be 4a,etc
Set Name = .cells (2+i,1)
'discount for Name is in cell 3c, after it loops it will be 4c,etc
Set Dis = .cells(2+i,3)

'find the fee 'send the data you need to analyse to your function and return
the value
FeeResult = fee(Assets, Dis)

'now paste the feeReult where you want it
'assumed that it will be pasted in same worksheet in 3d, after it loops
result will be pasted into 4d,etc
..cells(2+i, 4) = FeeResult
End With

Next i

End sub


Function fee(Assets as double, Dis as double)
' calculates annual management fee
SetTier
Select Case Assets
Case 1 To 499999.99
fee = (Assets * Tier1) * (1*(1-dis))
Case 500000 To 999999.99
fee = (500000 * Tier1 + (Assets - 500000) * Tier2) * (1*(1-dis))



















"

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

x = ActiveCell.Offset(0, -1).Value
If isNumeric(x) then
fee = fee * (1 - x)
End If

On Sun, 12 Sep 2004 16:06:32 -0700, robert burger
wrote:

Good evening all,
After a full week of searching and trying on my own I give up!
I know what I want but I can't write it, it's very frustrating!!

If anyone has some time to help me it would be appreciated.

Below is my commission model. I want to be able to add an if then
statement to it. The catch is, I want the if then statement to look at
an offset cell besides the active function cell and if it has a number
(discount) in it then apply, if not carry on as normal.

Public Tier1 As Double
Public Tier2 As Double
Public Tier3 As Double
Public Tier4 As Double
Public Tier5 As Double

Sub SetTier()
Tier1 = Worksheets("Fee schedule").Range("D3").Value
Tier2 = Worksheets("Fee schedule").Range("E3").Value
Tier3 = Worksheets("Fee schedule").Range("F3").Value
Tier4 = Worksheets("Fee schedule").Range("G3").Value
Tier5 = Worksheets("Fee schedule").Range("H3").Value
End Sub

Function fee(Assets)
' calculates annual management fee
SetTier
Select Case Assets
Case 1 To 499999.99
fee = Assets * Tier1
Case 500000 To 999999.99
fee = 500000 * Tier1 + (Assets - 500000) * Tier2
Case 1000000 To 1999999.99
fee = 500000 * Tier1 + 500000 * Tier2 + (Assets - 1000000) *
Tier3
Case 2000000 To 5000000
fee = 500000 * Tier1 + 500000 * Tier2 + 1000000 * Tier3 + _
(Assets - 2000000) * Tier4
Case Is = 5000000
fee = 500000 * Tier1 + 500000 * Tier2 + 1000000 * Tier3 + _
3000000 * Tier4 + (Assets - 5000000) * Tier5
End Select
End Function

The if statement will allow me to have a variable fee since I have more
than one client and they can all have different fee's.

If I have not made myself clear or if you think there is a better way of
doing this please say so.

Thanks again.

RTB

Myrna,
Thanks for the reply. I think what you wrote is my answer! Since I'm
new at VBA I'm not exactly sure how I would incorporate this into my
code. Would you be kind enough to explain how?
thanks,
Robert


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I need to see your existing code and more about your layout. You have maybe
provided that in another response. I will look at it later today if someone
else hasn't come up with your solution. BTW, I would use a VLOOKUP table for
the amounts instead of hard-codeing in the VBA. That way, if the cutoffs
change, you can just change the table without modifying the VBA code.


On Sun, 12 Sep 2004 17:17:03 -0700, robert burger
wrote:

Myrna,
Thanks for the reply. I think what you wrote is my answer! Since I'm
new at VBA I'm not exactly sure how I would incorporate this into my
code. Would you be kind enough to explain how?
thanks,
Robert


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!