code running slow , but gets faster when i hit esc

P

Pjam

Board Regular
Joined
Jun 4, 2008
Messages
139
HI,

i have a model that pulls in data to a template and re-calculates then spits out results onto a summary sheet. for some reason the code just started running very slow but when i hit escape to try and debug or click on the worksheet , the code starts to run quicker i am not sure why this is happening or why my model keeps runnning slower and slower i do set the variables equal to nothing to clear memory. Any ideas ?

Thanks
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Code: 
Option Explicit
Public wb               As Workbook
Public wsTemplate       As Worksheet
Public wsSummary        As Worksheet
Public wsLeaseTerms     As Worksheet
Public wsAssumptions    As Worksheet
Public Lease()          As New clsTemplate
Public Sub DefineTabs()
    Set wb = ThisWorkbook
    Set wsTemplate = wb.Worksheets("Template")
    Set wsLeaseTerms = wb.Worksheets("Lease Terms")
    Set wsSummary = wb.Worksheets("Summary")
    Set wsAssumptions = wb.Worksheets("Assumptions")
End Sub
Public Sub GetOneLeaseData()
    DefineTabs
    Call LoadData(wsTemplate.Range("RefKey") + 2)
    Application.Calculate
End Sub
Public Sub RunIterations()
 
    Application.ScreenUpdating = False
    DefineTabs
    wsSummary.Range("A3:K5000").ClearContents
    
    Dim LeaseCount As Integer
    LeaseCount = Application.WorksheetFunction.CountA(ThisWorkbook.Worksheets("Lease Terms").Range("c2:SH2"))
    Dim nLease As Integer
    
    For nLease = 1 To LeaseCount
        Application.StatusBar = "Running: " & VBA.Format(nLease / LeaseCount, "0%") & _
        "    Lease " & VBA.Format(nLease, "#,#") & " of " & VBA.Format(LeaseCount, "#,#")
    
        ReDim Preserve Lease(1 To nLease) As New clsTemplate
        Set Lease(nLease) = Nothing
        
        Call LoadData(nLease + 2)
        Application.Calculate
        Call Lease(nLease).GetValues
    Next nLease
    
    Call rptResults
    
    Application.StatusBar = False
    Application.ScreenUpdating = True
End Sub
Public Sub rptResults()
    Dim nItem           As Integer
    Dim outputArray()   As Variant
    Dim LeaseCount As Variant
    LeaseCount = ((Application.WorksheetFunction.CountA(ThisWorkbook.Worksheets("Lease Terms").Range("c2:SH2"))))
    
    
    ReDim outputArray(LeaseCount - 1, 10) As Variant
    
    For nItem = 1 To LeaseCount
        Application.StatusBar = "Running: " & VBA.Format(Round(nItem / CLng(LeaseCount), 2), "0%") & _
        "    Lease: " & VBA.Format(nItem, "#,#") & " of " & VBA.Format(LeaseCount, "#,#")
        
        outputArray(nItem - 1, 0) = nItem
        outputArray(nItem - 1, 1) = Lease(nItem).RestaurantNumber
        outputArray(nItem - 1, 2) = Lease(nItem).LeaseNumber
        outputArray(nItem - 1, 3) = Lease(nItem).Capital
        outputArray(nItem - 1, 4) = Lease(nItem).IncomePayable
        outputArray(nItem - 1, 5) = Lease(nItem).FavorableFlag
        outputArray(nItem - 1, 6) = Lease(nItem).PVCashflow
        outputArray(nItem - 1, 7) = Lease(nItem).Capital_Liability
        outputArray(nItem - 1, 8) = Lease(nItem).Capital_Right
        outputArray(nItem - 1, 9) = Lease(nItem).DFL_Residual
        outputArray(nItem - 1, 10) = Lease(nItem).AmortTerm
        
        Set Lease(nItem) = Nothing
    Next nItem
    Dim xlOutputRng     As Range
    Set xlOutputRng = ThisWorkbook.Worksheets("Summary").Range("A3")
    xlOutputRng.Resize(UBound(outputArray, 1) + 1, 11).Value = outputArray
    ThisWorkbook.Worksheets("Summary").Calculate
End Sub

Public Sub LoadData(i As Integer)
    With wsTemplate
    
        .Cells(9, 3) = Trim(wsLeaseTerms.Cells(2, i).Value)
        .Cells(10, 3) = Trim(wsLeaseTerms.Cells(3, i).Value)
        .Cells(11, 3) = Trim(wsLeaseTerms.Cells(4, i).Value)
        .Cells(12, 3) = Trim(wsLeaseTerms.Cells(5, i).Value)
        .Cells(13, 3) = Trim(wsLeaseTerms.Cells(6, i).Value)
        
        
        
        .Cells(14, 3) = Trim(wsLeaseTerms.Cells(7, i).Value)
        .Cells(15, 3) = Trim(wsLeaseTerms.Cells(8, i).Value)
        .Cells(16, 3) = Trim(wsLeaseTerms.Cells(9, i).Value) ' Capital Lease
        
        
        
        
        .Cells(17, 3) = Trim(wsLeaseTerms.Cells(10, i).Value)
        .Cells(18, 3) = Trim(wsLeaseTerms.Cells(11, i).Value)
        .Cells(19, 3) = Trim(wsLeaseTerms.Cells(12, i).Value)
        .Cells(20, 3) = Trim(wsLeaseTerms.Cells(13, i).Value)
        .Cells(21, 3) = Trim(wsLeaseTerms.Cells(14, i).Value)
        .Cells(22, 3) = Trim(wsLeaseTerms.Cells(15, i).Value)
        .Cells(23, 3) = Trim(wsLeaseTerms.Cells(16, i).Value)
        .Cells(24, 3) = Trim(wsLeaseTerms.Cells(17, i).Value)
        .Cells(25, 3) = Trim(wsLeaseTerms.Cells(18, i).Value)
        .Cells(26, 3) = Trim(wsLeaseTerms.Cells(19, i).Value)
        .Cells(27, 3) = Trim(wsLeaseTerms.Cells(20, i).Value)
        .Cells(28, 3) = Trim(wsLeaseTerms.Cells(21, i).Value)
        .Cells(29, 3) = Trim(wsLeaseTerms.Cells(22, i).Value)
        .Cells(30, 3) = Trim(wsLeaseTerms.Cells(23, i).Value)
        .Cells(31, 3) = wsLeaseTerms.Cells(24, i).Value
        .Cells(32, 3) = wsLeaseTerms.Cells(25, i).Value
        .Cells(33, 3) = wsLeaseTerms.Cells(26, 3).Value
       
        
        .Cells(69, 3) = IIf(wsLeaseTerms.Cells(62, i).Value = 0, "", wsLeaseTerms.Cells(62, i).Value)
        .Cells(70, 3) = IIf(wsLeaseTerms.Cells(63, i).Value = 0, "", wsLeaseTerms.Cells(63, i).Value)
        .Cells(71, 3) = IIf(wsLeaseTerms.Cells(64, i).Value = 0, "", wsLeaseTerms.Cells(64, i).Value)
        .Cells(72, 3) = IIf(wsLeaseTerms.Cells(65, i).Value = 0, "", wsLeaseTerms.Cells(65, i).Value) '  00000
        .Cells(73, 3) = IIf(wsLeaseTerms.Cells(66, i).Value = 0, "", wsLeaseTerms.Cells(66, i).Value)
        .Cells(74, 3) = IIf(wsLeaseTerms.Cells(67, i).Value = 0, "", wsLeaseTerms.Cells(67, i).Value)
        .Cells(75, 3) = IIf(wsLeaseTerms.Cells(68, i).Value = 0, "", wsLeaseTerms.Cells(68, i).Value)
        .Cells(76, 3) = IIf(wsLeaseTerms.Cells(69, i).Value = 0, "", wsLeaseTerms.Cells(69, i).Value)
        .Cells(77, 3) = IIf(wsLeaseTerms.Cells(70, i).Value = 0, "", wsLeaseTerms.Cells(70, i).Value)
        .Cells(78, 3) = IIf(wsLeaseTerms.Cells(71, i).Value = 0, "", wsLeaseTerms.Cells(71, i).Value)
        .Cells(79, 3) = IIf(wsLeaseTerms.Cells(72, i).Value = 0, "", wsLeaseTerms.Cells(72, i).Value)
        .Cells(80, 3) = IIf(wsLeaseTerms.Cells(73, i).Value = 0, "", wsLeaseTerms.Cells(73, i).Value)
        .Cells(81, 3) = IIf(wsLeaseTerms.Cells(74, i).Value = 0, "", wsLeaseTerms.Cells(74, i).Value)
        .Cells(82, 3) = IIf(wsLeaseTerms.Cells(75, i).Value = 0, "", wsLeaseTerms.Cells(75, i).Value)
        .Cells(83, 3) = IIf(wsLeaseTerms.Cells(76, i).Value = 0, "", wsLeaseTerms.Cells(76, i).Value)
        .Cells(84, 3) = IIf(wsLeaseTerms.Cells(77, i).Value = 0, "", wsLeaseTerms.Cells(77, i).Value)
        .Cells(85, 3) = IIf(wsLeaseTerms.Cells(78, i).Value = 0, "", wsLeaseTerms.Cells(78, i).Value)
        .Cells(86, 3) = IIf(wsLeaseTerms.Cells(79, i).Value = 0, "", wsLeaseTerms.Cells(79, i).Value)
        .Cells(87, 3) = IIf(wsLeaseTerms.Cells(80, i).Value = 0, "", wsLeaseTerms.Cells(80, i).Value)
        .Cells(88, 3) = IIf(wsLeaseTerms.Cells(81, i).Value = 0, "", wsLeaseTerms.Cells(81, i).Value)
        .Cells(89, 3) = IIf(wsLeaseTerms.Cells(82, i).Value = 0, "", wsLeaseTerms.Cells(82, i).Value)
        .Cells(90, 3) = IIf(wsLeaseTerms.Cells(83, i).Value = 0, "", wsLeaseTerms.Cells(83, i).Value)
        .Cells(91, 3) = IIf(wsLeaseTerms.Cells(84, i).Value = 0, "", wsLeaseTerms.Cells(84, i).Value)
        .Cells(92, 3) = IIf(wsLeaseTerms.Cells(85, i).Value = 0, "", wsLeaseTerms.Cells(85, i).Value)
        .Cells(93, 3) = IIf(wsLeaseTerms.Cells(86, i).Value = 0, "", wsLeaseTerms.Cells(86, i).Value)
        .Cells(94, 3) = IIf(wsLeaseTerms.Cells(87, i).Value = 0, "", wsLeaseTerms.Cells(87, i).Value)
        .Cells(95, 3) = IIf(wsLeaseTerms.Cells(88, i).Value = 0, "", wsLeaseTerms.Cells(88, i).Value)
        .Cells(96, 3) = IIf(wsLeaseTerms.Cells(89, i).Value = 0, "", wsLeaseTerms.Cells(89, i).Value)
        .Cells(97, 3) = IIf(wsLeaseTerms.Cells(90, i).Value = 0, "", wsLeaseTerms.Cells(90, i).Value)
        .Cells(98, 3) = IIf(wsLeaseTerms.Cells(91, i).Value = 0, "", wsLeaseTerms.Cells(91, i).Value)
        .Cells(99, 3) = IIf(wsLeaseTerms.Cells(92, i).Value = 0, "", wsLeaseTerms.Cells(92, i).Value)
        .Cells(100, 3) = IIf(wsLeaseTerms.Cells(93, i).Value = 0, "", wsLeaseTerms.Cells(93, i).Value)
        .Cells(101, 3) = IIf(wsLeaseTerms.Cells(94, i).Value = 0, "", wsLeaseTerms.Cells(94, i).Value) ' ''' 35Base RentFrom
        .Cells(102, 3) = IIf(wsLeaseTerms.Cells(95, i).Value = 0, "", wsLeaseTerms.Cells(95, i).Value)
        .Cells(103, 3) = IIf(wsLeaseTerms.Cells(96, i).Value = 0, "", wsLeaseTerms.Cells(96, i).Value)
        .Cells(104, 3) = IIf(wsLeaseTerms.Cells(97, i).Value = 0, "", wsLeaseTerms.Cells(97, i).Value)
        .Cells(105, 3) = IIf(wsLeaseTerms.Cells(98, i).Value = 0, "", wsLeaseTerms.Cells(98, i).Value)
        .Cells(106, 3) = IIf(wsLeaseTerms.Cells(99, i).Value = 0, "", wsLeaseTerms.Cells(99, i).Value)
        .Cells(107, 3) = IIf(wsLeaseTerms.Cells(100, i).Value = 0, "", wsLeaseTerms.Cells(100, i).Value)
        .Cells(108, 3) = IIf(wsLeaseTerms.Cells(101, i).Value = 0, "", wsLeaseTerms.Cells(101, i).Value)
        .Cells(109, 3) = IIf(wsLeaseTerms.Cells(102, i).Value = 0, "", wsLeaseTerms.Cells(102, i).Value)
        .Cells(110, 3) = IIf(wsLeaseTerms.Cells(103, i).Value = 0, "", wsLeaseTerms.Cells(103, i).Value)
        .Cells(111, 3) = IIf(wsLeaseTerms.Cells(104, i).Value = 0, "", wsLeaseTerms.Cells(104, i).Value)
        .Cells(112, 3) = IIf(wsLeaseTerms.Cells(105, i).Value = 0, "", wsLeaseTerms.Cells(105, i).Value)
        .Cells(113, 3) = IIf(wsLeaseTerms.Cells(106, i).Value = 0, "", wsLeaseTerms.Cells(106, i).Value)
        .Cells(114, 3) = IIf(wsLeaseTerms.Cells(107, i).Value = 0, "", wsLeaseTerms.Cells(107, i).Value)
        .Cells(115, 3) = IIf(wsLeaseTerms.Cells(108, i).Value = 0, "", wsLeaseTerms.Cells(108, i).Value)
        .Cells(116, 3) = IIf(wsLeaseTerms.Cells(109, i).Value = 0, "", wsLeaseTerms.Cells(109, i).Value)
        
        .Cells(117, 3) = IIf(wsLeaseTerms.Cells(110, i).Value = 0, "", wsLeaseTerms.Cells(110, i).Value)
        .Cells(118, 3) = IIf(wsLeaseTerms.Cells(111, i).Value = 0, "", wsLeaseTerms.Cells(111, i).Value)
        .Cells(119, 3) = IIf(wsLeaseTerms.Cells(112, i).Value = 0, "", wsLeaseTerms.Cells(112, i).Value)
        .Cells(120, 3) = IIf(wsLeaseTerms.Cells(113, i).Value = 0, "", wsLeaseTerms.Cells(113, i).Value)
        .Cells(121, 3) = IIf(wsLeaseTerms.Cells(114, i).Value = 0, "", wsLeaseTerms.Cells(114, i).Value)
        .Cells(122, 3) = IIf(wsLeaseTerms.Cells(115, i).Value = 0, "", wsLeaseTerms.Cells(115, i).Value)
        .Cells(123, 3) = IIf(wsLeaseTerms.Cells(116, i).Value = 0, "", wsLeaseTerms.Cells(116, i).Value)
        .Cells(124, 3) = IIf(wsLeaseTerms.Cells(117, i).Value = 0, "", wsLeaseTerms.Cells(117, i).Value)
        .Cells(125, 3) = IIf(wsLeaseTerms.Cells(118, i).Value = 0, "", wsLeaseTerms.Cells(118, i).Value)
        .Cells(126, 3) = IIf(wsLeaseTerms.Cells(119, i).Value = 0, "", wsLeaseTerms.Cells(119, i).Value)
        .Cells(127, 3) = IIf(wsLeaseTerms.Cells(120, i).Value = 0, "", wsLeaseTerms.Cells(120, i).Value)
        .Cells(128, 3) = IIf(wsLeaseTerms.Cells(121, i).Value = 0, "", wsLeaseTerms.Cells(121, i).Value)
        .Cells(129, 3) = IIf(wsLeaseTerms.Cells(122, i).Value = 0, "", wsLeaseTerms.Cells(122, i).Value)
        .Cells(130, 3) = IIf(wsLeaseTerms.Cells(123, i).Value = 0, "", wsLeaseTerms.Cells(123, i).Value)
        .Cells(131, 3) = IIf(wsLeaseTerms.Cells(124, i).Value = 0, "", wsLeaseTerms.Cells(124, i).Value)
        .Cells(132, 3) = IIf(wsLeaseTerms.Cells(125, i).Value = 0, "", wsLeaseTerms.Cells(125, i).Value)
        .Cells(133, 3) = IIf(wsLeaseTerms.Cells(126, i).Value = 0, "", wsLeaseTerms.Cells(126, i).Value)
        .Cells(134, 3) = IIf(wsLeaseTerms.Cells(127, i).Value = 0, "", wsLeaseTerms.Cells(127, i).Value)
        .Cells(135, 3) = IIf(wsLeaseTerms.Cells(128, i).Value = 0, "", wsLeaseTerms.Cells(128, i).Value)
        .Cells(136, 3) = IIf(wsLeaseTerms.Cells(129, i).Value = 0, "", wsLeaseTerms.Cells(129, i).Value)
        .Cells(137, 3) = IIf(wsLeaseTerms.Cells(130, i).Value = 0, "", wsLeaseTerms.Cells(130, i).Value)
        .Cells(138, 3) = IIf(wsLeaseTerms.Cells(131, i).Value = 0, "", wsLeaseTerms.Cells(131, i).Value) ' '''''''''' 35 Base RentTo
        
        
        
        
.Cells(139, 3) = wsLeaseTerms.Cells(132, i).Value
.Cells(140, 3) = wsLeaseTerms.Cells(133, i).Value
.Cells(141, 3) = wsLeaseTerms.Cells(134, i).Value
.Cells(142, 3) = wsLeaseTerms.Cells(135, i).Value
.Cells(143, 3) = wsLeaseTerms.Cells(136, i).Value
.Cells(144, 3) = wsLeaseTerms.Cells(137, i).Value
.Cells(145, 3) = wsLeaseTerms.Cells(138, i).Value
.Cells(146, 3) = wsLeaseTerms.Cells(139, i).Value
.Cells(147, 3) = wsLeaseTerms.Cells(140, i).Value
.Cells(148, 3) = wsLeaseTerms.Cells(141, i).Value
.Cells(149, 3) = wsLeaseTerms.Cells(142, i).Value
.Cells(150, 3) = wsLeaseTerms.Cells(143, i).Value
.Cells(151, 3) = wsLeaseTerms.Cells(144, i).Value
.Cells(152, 3) = wsLeaseTerms.Cells(145, i).Value
.Cells(153, 3) = wsLeaseTerms.Cells(146, i).Value
.Cells(154, 3) = wsLeaseTerms.Cells(147, i).Value
.Cells(155, 3) = wsLeaseTerms.Cells(148, i).Value
.Cells(156, 3) = wsLeaseTerms.Cells(149, i).Value
.Cells(157, 3) = wsLeaseTerms.Cells(150, i).Value
.Cells(158, 3) = wsLeaseTerms.Cells(151, i).Value
.Cells(159, 3) = wsLeaseTerms.Cells(152, i).Value
.Cells(160, 3) = wsLeaseTerms.Cells(153, i).Value
.Cells(161, 3) = wsLeaseTerms.Cells(154, i).Value
.Cells(162, 3) = wsLeaseTerms.Cells(155, i).Value
.Cells(163, 3) = wsLeaseTerms.Cells(156, i).Value
.Cells(164, 3) = wsLeaseTerms.Cells(157, i).Value
.Cells(165, 3) = wsLeaseTerms.Cells(158, i).Value
.Cells(166, 3) = wsLeaseTerms.Cells(159, i).Value
.Cells(167, 3) = wsLeaseTerms.Cells(160, i).Value
.Cells(168, 3) = wsLeaseTerms.Cells(161, i).Value
.Cells(169, 3) = wsLeaseTerms.Cells(162, i).Value
.Cells(170, 3) = wsLeaseTerms.Cells(163, i).Value
.Cells(171, 3) = wsLeaseTerms.Cells(164, i).Value
.Cells(172, 3) = wsLeaseTerms.Cells(165, i).Value
.Cells(173, 3) = wsLeaseTerms.Cells(166, i).Value ' ''''35Base Rent Rent
.Cells(174, 3) = wsLeaseTerms.Cells(167, i).Value
.Cells(175, 3) = wsLeaseTerms.Cells(168, i).Value
.Cells(176, 3) = wsLeaseTerms.Cells(169, i).Value
.Cells(177, 3) = wsLeaseTerms.Cells(170, i).Value
.Cells(178, 3) = wsLeaseTerms.Cells(171, i).Value
.Cells(179, 3) = wsLeaseTerms.Cells(172, i).Value
.Cells(180, 3) = wsLeaseTerms.Cells(173, i).Value
.Cells(181, 3) = wsLeaseTerms.Cells(174, i).Value
.Cells(182, 3) = wsLeaseTerms.Cells(175, i).Value
.Cells(183, 3) = wsLeaseTerms.Cells(176, i).Value
.Cells(184, 3) = wsLeaseTerms.Cells(177, i).Value
.Cells(185, 3) = wsLeaseTerms.Cells(178, i).Value
.Cells(186, 3) = wsLeaseTerms.Cells(179, i).Value
.Cells(187, 3) = wsLeaseTerms.Cells(180, i).Value
.Cells(188, 3) = wsLeaseTerms.Cells(181, i).Value
.Cells(189, 3) = wsLeaseTerms.Cells(182, i).Value
.Cells(190, 3) = wsLeaseTerms.Cells(183, i).Value
.Cells(191, 3) = wsLeaseTerms.Cells(184, i).Value
.Cells(192, 3) = wsLeaseTerms.Cells(185, i).Value
.Cells(193, 3) = wsLeaseTerms.Cells(186, i).Value
.Cells(194, 3) = wsLeaseTerms.Cells(187, i).Value
.Cells(195, 3) = wsLeaseTerms.Cells(188, i).Value
.Cells(196, 3) = wsLeaseTerms.Cells(189, i).Value
.Cells(197, 3) = wsLeaseTerms.Cells(190, i).Value
.Cells(198, 3) = wsLeaseTerms.Cells(191, i).Value
.Cells(199, 3) = wsLeaseTerms.Cells(192, i).Value
.Cells(200, 3) = wsLeaseTerms.Cells(193, i).Value
.Cells(201, 3) = wsLeaseTerms.Cells(194, i).Value
.Cells(202, 3) = wsLeaseTerms.Cells(195, i).Value
.Cells(203, 3) = wsLeaseTerms.Cells(196, i).Value
.Cells(204, 3) = wsLeaseTerms.Cells(197, i).Value
.Cells(205, 3) = wsLeaseTerms.Cells(198, i).Value
.Cells(206, 3) = wsLeaseTerms.Cells(199, i).Value
.Cells(207, 3) = wsLeaseTerms.Cells(200, i).Value
.Cells(208, 3) = wsLeaseTerms.Cells(201, i).Value '''35Percent Rent
    
.Cells(209, 3) = wsLeaseTerms.Cells(202, i).Value
.Cells(210, 3) = wsLeaseTerms.Cells(203, i).Value
.Cells(211, 3) = wsLeaseTerms.Cells(204, i).Value
.Cells(212, 3) = wsLeaseTerms.Cells(205, i).Value
.Cells(213, 3) = wsLeaseTerms.Cells(206, i).Value
.Cells(214, 3) = wsLeaseTerms.Cells(207, i).Value
.Cells(215, 3) = wsLeaseTerms.Cells(208, i).Value
.Cells(216, 3) = wsLeaseTerms.Cells(209, i).Value
.Cells(217, 3) = wsLeaseTerms.Cells(210, i).Value
.Cells(218, 3) = wsLeaseTerms.Cells(211, i).Value
.Cells(219, 3) = wsLeaseTerms.Cells(212, i).Value
.Cells(220, 3) = wsLeaseTerms.Cells(213, i).Value
.Cells(221, 3) = wsLeaseTerms.Cells(214, i).Value
.Cells(222, 3) = wsLeaseTerms.Cells(215, i).Value
.Cells(223, 3) = wsLeaseTerms.Cells(216, i).Value
.Cells(224, 3) = wsLeaseTerms.Cells(217, i).Value
.Cells(225, 3) = wsLeaseTerms.Cells(218, i).Value
.Cells(226, 3) = wsLeaseTerms.Cells(219, i).Value
.Cells(227, 3) = wsLeaseTerms.Cells(220, i).Value
.Cells(228, 3) = wsLeaseTerms.Cells(221, i).Value
.Cells(229, 3) = wsLeaseTerms.Cells(222, i).Value
.Cells(230, 3) = wsLeaseTerms.Cells(223, i).Value
.Cells(231, 3) = wsLeaseTerms.Cells(224, i).Value
.Cells(232, 3) = wsLeaseTerms.Cells(225, i).Value
.Cells(233, 3) = wsLeaseTerms.Cells(226, i).Value
.Cells(234, 3) = wsLeaseTerms.Cells(227, i).Value
.Cells(235, 3) = wsLeaseTerms.Cells(228, i).Value
.Cells(236, 3) = wsLeaseTerms.Cells(229, i).Value
.Cells(237, 3) = wsLeaseTerms.Cells(230, i).Value
.Cells(238, 3) = wsLeaseTerms.Cells(231, i).Value
.Cells(239, 3) = wsLeaseTerms.Cells(232, i).Value
.Cells(240, 3) = wsLeaseTerms.Cells(233, i).Value
.Cells(241, 3) = wsLeaseTerms.Cells(234, i).Value
.Cells(242, 3) = wsLeaseTerms.Cells(235, i).Value
.Cells(243, 3) = wsLeaseTerms.Cells(236, i).Value '35 Term
    
    
    
    
    
.Cells(244, 3) = wsAssumptions.Cells(3, 3).Value
.Cells(245, 3) = wsAssumptions.Cells(4, 3).Value
.Cells(246, 3) = wsAssumptions.Cells(5, 3).Value
.Cells(247, 3) = wsAssumptions.Cells(8, 3).Value
    End With
End Sub
'++++++++++++++++++++++++++++++++++++++++++++++++++
 
Upvote 0
You have one for loop. Why aren't you looping through the rest?

I don't know if that will actually speed it up, but it should at least be worth a shot.
 
Upvote 0
You must log in or register to reply here.

Similar threads

JeffGrant
  • Solved
When is a workbook too big?
2
Replies
11
Views
2K
Michael M
Michael M
H
  • Question Question
How do I avoid a 1004 Runtime Error when trying to run this script?
Replies
3
Views
992
helpneeded2
H
P
  • Question Question
Runtime Error 462 when Running Code to Export Excel Values into Word Bookmarks
Replies
6
Views
1K
pastelprincess
P
K
  • Question Question
Finally tracked down the source/reason responsible for how I keep getting this error... now I need to understand WHY its happening...(?)
Replies
8
Views
845
kbishop94
K
starl
  • Solved
VBE says there's code running but I can't find the code - then Excel crashes
Replies
6
Views
760
starl
starl

Forum statistics

Threads
1,224,564
Messages
6,179,548
Members
452,927
Latest member
rows and columns

Share this page

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
Back
Top