Guidance, please (xl2007)
 

Problem: when I run the sub, I get the message "sub or function not defined",
the problem appears to be related to the statement assessing, which is the
minimum value. Cant I use the Min statement like this, or where might the
trouble be?

Thx very much!

Code:

Sub Optimize_pension_A()

Dim startyear, number_yrs As Integer
Dim potential, max_finansing, maks_contribution As Single

h = 1 ' contant used in function
i = 0
startyear = cells(78, 2).Value 'startyear
number_yrs = cells(3, 10).Value - cells(6, 10).Value 'years to retirement,
person A
max_contribution = 45300
max_financing= cells(69, 2).Value '(budgetdeficit)

For i = 0 To number_yrs

If cells(60, 2 + i).Value 0 Then potential= Min(max_contribution,
(cells(60, 2 + i))) Else Exit For


cells(80, 2 + i).Value = -potentiale

finansing = cells(99, 2 + i).Value
If finansing <= max_finansing Then Result = Find_finansiering_A(cells(80, 2
+ i), cells(99, 2 + i)) Else

Next i

End Sub

Guidance, please (xl2007)
 

Hi Kragelund

To use a worksheetfunction in a macro use this syntax:

Application.WorksheetFunction.Min(....

Regards,
Per

On 7 Dec., 23:43, Kragelund
wrote:
Problem: when I run the sub, I get the message "sub or function not defined",
the problem appears to be related to the statement assessing, which is the
minimum value. Cant I use the Min statement like this, or where might the
trouble be?

Thx very much!

Code:

Sub Optimize_pension_A()

Dim startyear, number_yrs As Integer
Dim potential, max_finansing, maks_contribution As Single

h = 1 * * * * * * * * * * * * * * * * * * * * * * * ' contant used in function
i = 0
startyear = cells(78, 2).Value * * * * * * * * * * * *'startyear
number_yrs = cells(3, 10).Value - cells(6, 10).Value *'years to retirement,
person A
max_contribution = 45300 * * * * * * * * * * * * * *
max_financing= cells(69, 2).Value * * * * * * *'(budgetdeficit)

For i = 0 To number_yrs

If cells(60, 2 + i).Value 0 Then potential= Min(max_contribution,
(cells(60, 2 + i))) Else Exit For

cells(80, 2 + i).Value = -potentiale

finansing = cells(99, 2 + i).Value
If finansing <= max_finansing Then Result = Find_finansiering_A(cells(80, 2
+ i), cells(99, 2 + i)) Else

Next i

End Sub

Guidance, please (xl2007)
 

You need the qualifier WorksheetFunction

If cells(60, 2 + i).Value 0 Then potential = _
WorksheetFunction.Min(max_contribution, +
(cells(60, 2 + i))) Else Exit For

The VBA Min property is used in relation to measurement and is not a
function, so you have to tell VBA when you want to use it as a function with
the qualifying statement.




"Kragelund" wrote:

Problem: when I run the sub, I get the message "sub or function not defined",
the problem appears to be related to the statement assessing, which is the
minimum value. Cant I use the Min statement like this, or where might the
trouble be?

Thx very much!

Code:

Sub Optimize_pension_A()

Dim startyear, number_yrs As Integer
Dim potential, max_finansing, maks_contribution As Single

h = 1 ' contant used in function
i = 0
startyear = cells(78, 2).Value 'startyear
number_yrs = cells(3, 10).Value - cells(6, 10).Value 'years to retirement,
person A
max_contribution = 45300
max_financing= cells(69, 2).Value '(budgetdeficit)

For i = 0 To number_yrs

If cells(60, 2 + i).Value 0 Then potential= Min(max_contribution,
(cells(60, 2 + i))) Else Exit For


cells(80, 2 + i).Value = -potentiale

finansing = cells(99, 2 + i).Value
If finansing <= max_finansing Then Result = Find_finansiering_A(cells(80, 2
+ i), cells(99, 2 + i)) Else

Next i

End Sub

Guidance, please (xl2007)
 

Also, you should add the expression "Option Explicit" (without the quote
marks) as the first line of the code module.
--
Jim Cone
Portland, Oregon USA



"Kragelund"
wrote in message
Problem: when I run the sub, I get the message "sub or function not defined",
the problem appears to be related to the statement assessing, which is the
minimum value. Cant I use the Min statement like this, or where might the
trouble be?
Thx very much!
Code:

Sub Optimize_pension_A()
Dim startyear, number_yrs As Integer
Dim potential, max_finansing, maks_contribution As Single

h = 1 ' contant used in function
i = 0
startyear = cells(78, 2).Value 'startyear
number_yrs = cells(3, 10).Value - cells(6, 10).Value 'years to retirement,
person A
max_contribution = 45300
max_financing= cells(69, 2).Value '(budgetdeficit)

For i = 0 To number_yrs
If cells(60, 2 + i).Value 0 Then potential= Min(max_contribution,
(cells(60, 2 + i))) Else Exit For
cells(80, 2 + i).Value = -potentiale
finansing = cells(99, 2 + i).Value
If finansing <= max_finansing Then Result = Find_finansiering_A(cells(80, 2
+ i), cells(99, 2 + i)) Else
Next i
End Sub

Guidance, please (xl2007)
 

Further...
Option Explicit will highlight those areas that need attention, such as...

max_contribution
maks_contribution

max_finansing
max_financing
finansing

potential
potentiale

Result
--

Jim Cone
Portland, Oregon USA

Guidance, please (xl2007)
 

wonderful, all, now it works, thanks. Good idea to use option explicit for
future use.

Kragelund

"Jim Cone" wrote:

Further...
Option Explicit will highlight those areas that need attention, such as...

max_contribution
maks_contribution

max_finansing
max_financing
finansing

potential
potentiale

Result
--

Jim Cone
Portland, Oregon USA