VBA code bug

[wycyobm]

Hi, i am trying to run this code but is stops at Sub statement (2nd line TT) i am new to VBA , is it related to the worksheet name? I have changed it but it still does not run.

Option Explicit
Sub LoanAmort2()
'This program is used to measure annual payment amount
'by using an iterative process. It starts with an annual
'payment which is subjectively safe and then it changes
'costantly in same steps until the balance becomes negative
'at the end of the last year
Dim intRate As Single, initLoanAmnt As Single
Dim yrBegBal As Single, yrEngBal As Single
Dim intComp As Single, princRepay As Single
Dim annualPmnt As Single, annualIncr As Single
Dim loanLife As Integer, outRow As Integer
Dim rowNum1 As Integer, numOfIterations As Integer
Dim outSheet As String
'***********************************************************
'Inputs of the user
'***********************************************************
'Read in from data entered on worksheet
intRate = Cells(2, 2).Value 'In decimals
loanLife = Cells(3, 2).Value 'In full years
initLoanAmnt = Cells(4, 2).Value 'Initial loan amount
'***********************************************************
'Inputs of the programmer
'***********************************************************
annualIncr = initLoanAmnt / 1000 'step size for payment guess
outSheet = "Loan Amort"
outRow = 8 'row below which repayment schedule will start
'************************************************************
'Preliminaries
'************************************************************
'Make the outSheet the active sheet
Worksheet(outSheet).Activate
'Clear previous data
Rows(outRow +1 & ":" outRow +300).Select
Selection.Clear
'************************************************************
'Computations
'************************************************************
annualPmnt = initLoanAmnt / loanLife 'initial guess
numOfIterations = 0 'counter for number of iterations
'this do loop controls the iteration
Do While Cells(outRow + loanLife, 8).Value >= 0
'initialize balance at the beginning of year 1
yrBegBal = initLoanAmnt
'Loop to calculate and output year-by-year data
For rowNum1 = 1 To loanLife
intComp = yrBegBal * intRate
pricRepay = annualPmnt - intComp
yrEndBal = yrBegBal - princRepay
Cells(outRow + rowNum1, 3).Value = rowNum1
Cells(outRow + rowNum1, 4).Value = yrBegBal
Cells(outRow + rowNum1, 5).Value = annualPmnt
Cells(outRow + rowNum1, 6).Value = intComp
Cells(outRow + rowNum1, 7).Value = princRepay
Cells(outRow + rowNum1, 8).Value = yrEndBal
yrBegBal = yrEndBal 'setting up for next year
Next rowNum1
annualPmnt = annualPmnt + annualIncr 'new annual payment
numOfIterations = numOfIterations + 1 'count iterations
Loop 'End of the iterations Do loop
'Write out the number of iterations used
Cells(outRow + loanLife + 4, 1).Value = "No. of iterations"
Cells(outRow + loanLife + 4, 2).Value = numOfItereations
'**********************************************************
'format data in table output
'**********************************************************
Range(Cells(outRow + 1, 4), Cells(outRow + loanLife, 8)).Select
Selection.NumberFormat = "$#,##0"

End Sub

 

[Norie]

Do you get an error message?

 

[wycyobm]

Do you get an error message?

Hi, yes, it says sub or function not defined

 

[wenzi]

Worksheet(outSheet).Activate
'Clear previous data
Rows(outRow +1 & ":" outRow +300).Select
Selection.Clear

you need make sure active that worksheet befer select in that worksheet.

 

[wideboydixon]

It should be:

Worksheets(outSheet).Activate

WBD

 

[FormR]

And the very next line should probably be:

Rows(outRow + 1 & ":" & outRow + 300).Select

Plus you have some typos in your variable names - I'm sure you'll spot them when you try to compile the code..

 

[wycyobm]

Worksheet(outSheet).Activate
'Clear previous data
Rows(outRow +1 & ":" outRow +300).Select
Selection.Clear

you need make sure active that worksheet befer select in that worksheet.

Hi, which worksheet should i activate? and how can i activate?

 

[wycyobm]

And the very next line should probably be:

Rows(outRow + 1 & ":" & outRow + 300).Select

Plus you have some typos in your variable names - I'm sure you'll spot them when you try to compile the code..

Thanks, i have noticed a lot of spelling mistakes x
now it runs until

[COLOR=#FF0000]annualPmnt = initLoanAmnt / loanLife[/COLOR]

it says OVERFLOW, how can i resolve it? I have nothing on my worksheet

 

[Norie]

If you have nothing on the sheet then loanLife will be 0 and you can't divide by 0.

 

[wycyobm]

If you have nothing on the sheet then loanLife will be 0 and you can't divide by 0.

Hi, i have just put some numbers but do i have to put them with formulas?? because it still says overflow