Loan Ammortization VBA for multiple Loans

[aflynn]

Hello,

I have an ammortization calculator that I built that utilizes a sum function based on a date range built into the function, so if a loan is delinquent by x number of months that is how many months principal and interest is summed.

The loan info is stored in columns A3:H3

I am trying to create a macro that would copy the loan info from A3:H3 to L3:S3 then copy the output info in T3:V3 into I3:K3 - then loop fo hundreds of loans, pasting the output to the corresponding loan number.

I have tried
Sub ABC()
'
' Select cell A2, *first line of data*.
'Range("A3").Select
' Set Do loop to stop when an empty cell is reached.
'Do Until IsEmpty(ActiveCell)
For i = 1 To 2000

Range("A3:H3" & i).Copy Range("l3")

Range("T3:V3").Copy
Range("I3" & i).PasteSpecial Paste:=xlPasteValues
Next i

Application.CutCopyMode = False
End Sub
and I have tried
Sub ABCD()
'
' ABCD Macro
'

'
Range("A3:H3").Select
Selection.Copy
ActiveWindow.ScrollColumn = 2
Range("M3").Select
Range("L3:S3").Select
ActiveSheet.Paste
Range("T3:V3").Select
Application.CutCopyMode = False
Selection.Copy
Range("I3").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A4:H4").Select
Application.CutCopyMode = False
Selection.Copy
Range("L3:S3").Select
ActiveSheet.Paste
Range("T3:V3").Select
Application.CutCopyMode = False
Selection.Copy
Range("I4").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A5:H5").Select
Application.CutCopyMode = False
Selection.Copy
Range("L3").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
Range("T3:V3").Select
Application.CutCopyMode = False
Selection.Copy
Range("I5").Select
ActiveSheet.Paste

Then adding the loop function to this ( sorry, lost the actual code I used in the shuffle as I have been trying everything I could). I have searched for this and nothing seems to be working, I feel like this should be simple but I am no expert in macro building. Any help would be greatly appreciated. Thank you>

 

[Norie]

Perhaps something like this.

Option Explicit

Sub CalculateLoans()
Dim rngSrc As Range
Dim rngCalc As Range

    Set rngSrc = Range("A3:H3")
    
    Set rngCalc = Range("L3")
    
    Do
        rngSrc.Copy rngCalc
        rngCalc.Offset(, 8).Resize(, 3).Copy rngSrc.Offset(, 8).Resize(, 3)
        Set rngSrc = rngSrc.Offset(1)
    Loop Until rngSrc.Cells(1, 1).Value = ""
    
End Sub

 

[aflynn]

Perhaps something like this.

Option Explicit

Sub CalculateLoans()
Dim rngSrc As Range
Dim rngCalc As Range

    Set rngSrc = Range("A3:H3")
   
    Set rngCalc = Range("L3")
   
    Do
        rngSrc.Copy rngCalc
        rngCalc.Offset(, 8).Resize(, 3).Copy rngSrc.Offset(, 8).Resize(, 3)
        Set rngSrc = rngSrc.Offset(1)
    Loop Until rngSrc.Cells(1, 1).Value = ""
   
End Sub

Thank you, this is amazing. I was concerned about joining the forum as I saw lots of answers like "try googling" but I appreciate you and the quick response and elegant code. I am having one issue with the code, it is pasting the formula I:K and I need the values without the formulas. Is it as easy as replacing copy with PasteSpecial xlPasteValues? When I tried that I got a syntax error, any ideas or resources I could use? Thank you again for taking the time to look at this!

 

[Norie]

Try this.

rngCalc.Offset(, 8).Resize(, 3).Copy 
rngSrc.Offset(, 8).Resize(, 3).PasteSpecial xlPasteValues

 

[aflynn]

Try this.

rngCalc.Offset(, 8).Resize(, 3).Copy
rngSrc.Offset(, 8).Resize(, 3).PasteSpecial xlPasteValues

Thank you again, this works perfectly!