Loan Ammortization VBA for multiple Loans

aflynn

New Member
Joined
Jan 29, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.
1611960625280.png

The loan info is stored in columns A3:H3
1611960743514.png

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>
 

Attachments

  • 1611960593756.png
    1611960593756.png
    31.5 KB · Views: 2

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,263
Office Version
  1. 365
Platform
  1. Windows
Perhaps something like this.
VBA Code:
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
 
Solution

aflynn

New Member
Joined
Jan 29, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Perhaps something like this.
VBA Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,263
Office Version
  1. 365
Platform
  1. Windows
Try this.
VBA Code:
rngCalc.Offset(, 8).Resize(, 3).Copy 
rngSrc.Offset(, 8).Resize(, 3).PasteSpecial xlPasteValues
 

aflynn

New Member
Joined
Jan 29, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Try this.
VBA Code:
rngCalc.Offset(, 8).Resize(, 3).Copy
rngSrc.Offset(, 8).Resize(, 3).PasteSpecial xlPasteValues
Thank you again, this works perfectly!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,683
Members
415,920
Latest member
ExcelNoob28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top