Helping a New VBA User With Efficiency in VBA

Shake728

New Member
Joined
Sep 4, 2019
Messages
4
I am a beginner to the world of VBA. I am attempting to automate a spread sheet that receives data from multiple, fluctuating tabs. I have created the code below but it basically is just inputting formulas in each cell that then calculates as though they were manually entered into each cell. Is there a better way to have this work or a more efficient way?

Code:
Sub ListSheets() 
Dim ws As Worksheet
Dim x As Integer
 
Set sws = ThisWorkbook.Sheets("Summary Workpaper")
x = 3
y = 4


Application.ScreenUpdating = False
 
sws.Range("c9:XL36").ClearContents
 
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Summary Workpaper" And ws.Name <> "Lease Template" And ws.Name <> "Disclosures" Then
        sws.Cells(9, x) = ws.Name
        sws.Range(Cells(9, x), Cells(9, x + 1)).Merge
        sws.Range(Cells(9, x), Cells(10, x + 1)).HorizontalAlignment = xlCenterAcrossSelection
        sws.Cells(10, x) = "Debit"
        sws.Cells(10, y) = "Credit"
        
            sws.Cells(12, x).FormulaR1C1 = _
            "=IFERROR(IF(0=INDIRECT(""'""&R[-3]C&""'!Termination_Extension_Date""),IF(EOMONTH(INDIRECT(""'""&R9C&""'!Lease_Start_date""),0)>EOMONTH(INDIRECT(""'""&R9C&""'!asu_adoption_date""),0),IF(EOMONTH(Current_Month_End,0)=EOMONTH(INDIRECT(""'""&R9C&""'!Lease_Start_date""),0),ROUND(INDIRECT(""'""&R9C&""'!Right_of_use_asset_value"")+INDIRECT(""'""&R9C&""'!Deferred_Rent"")," & _
            "2),0),IF(EOMONTH(Current_Month_End,0)=EOMONTH(INDIRECT(""'""&R9C&""'!asu_adoption_date""),0),ROUND(INDIRECT(""'""&R9C&""'!Right_of_use_asset_value"")+INDIRECT(""'""&R9C&""'!Deferred_Rent""),2),0)),IF(EOMONTH(Current_Month_End,-1)<indirect(""'""&r[-3]c&""'!termination_extension_date""),if(eomonth(indirect(""'""&r9c&""'!lease_start_date""),0)>EOMONTH(INDIRECT(""'""&R9" & _
            "C&""'!asu_adoption_date""),0),IF(EOMONTH(Current_Month_End,0)=EOMONTH(INDIRECT(""'""&R9C&""'!Lease_Start_date""),0),ROUND(INDIRECT(""'""&R9C&""'!Right_of_use_asset_value"")+INDIRECT(""'""&R9C&""'!Deferred_Rent""),2),0),IF(EOMONTH(Current_Month_End,0)=EOMONTH(INDIRECT(""'""&R9C&""'!asu_adoption_date""),0),ROUND(INDIRECT(""'""&R9C&""'!Right_of_use_asset_value"")+INDIR" & _
            "ECT(""'""&R9C&""'!Deferred_Rent""),2),0)),"""")),"""")" & _
            ""
            
            sws.Cells(13, x).FormulaR1C1 = _
            "=(IFERROR(-IF(0=INDIRECT(""'""&R[-4]C&""'!Termination_Extension_Date""),IF(EOMONTH(INDIRECT(""'""&R9C&""'!Lease_Start_date""),0)>EOMONTH(INDIRECT(""'""&R9C&""'!asu_adoption_date""),0),IF(EOMONTH(Current_Month_End,0)=EOMONTH(INDIRECT(""'""&R9C&""'!Lease_Start_date""),0),ROUND(INDIRECT(""'""&R9C&""'!Deferred_Rent""),2),0),IF(EOMONTH(Current_Month_End,0)=EOMONTH(IND" & _
            "IRECT(""'""&R9C&""'!asu_adoption_date""),0),ROUND(INDIRECT(""'""&R9C&""'!Deferred_Rent""),2),0)),IF(EOMONTH(Current_Month_End,-1)<indirect(""'""&r[-4]c&""'!termination_extension_date""),if(eomonth(indirect(""'""&r9c&""'!lease_start_date""),0)>EOMONTH(INDIRECT(""'""&R9C&""'!asu_adoption_date""),0),IF(EOMONTH(Current_Month_End,0)=EOMONTH(INDIRECT(""'""&R9C&""'!Lease_S" & _
            "tart_date""),0),ROUND(INDIRECT(""'""&R9C&""'!Deferred_Rent""),2),0),IF(EOMONTH(Current_Month_End,0)=EOMONTH(INDIRECT(""'""&R9C&""'!asu_adoption_date""),0),ROUND(INDIRECT(""'""&R9C&""'!Deferred_Rent""),2),0)),"""")),""""))" & _
            ""
            
            sws.Cells(14, y).FormulaR1C1 = _
            "=IFERROR(IF(0=INDIRECT(""'""&R[-5]C[-1]&""'!Termination_Extension_Date""),IF(EOMONTH(INDIRECT(""'""&R9C[-1]&""'!Lease_Start_date""),0)>EOMONTH(INDIRECT(""'""&R9C[-1]&""'!asu_adoption_date""),0),IF(EOMONTH(Current_Month_End,0)=EOMONTH(INDIRECT(""'""&R9C[-1]&""'!Lease_Start_date""),0),ROUND(INDIRECT(""'""&R9C[-1]&""'!Right_of_use_asset_value""),2),0),IF(EOMONTH(Cur" & _
            "rent_Month_End,0)=EOMONTH(INDIRECT(""'""&R9C[-1]&""'!asu_adoption_date""),0),ROUND(INDIRECT(""'""&R9C[-1]&""'!Right_of_use_asset_value""),2),0)),IF(EOMONTH(Current_Month_End,-1)<indirect(""'""&r[-5]c[-1]&""'!termination_extension_date""),if(eomonth(indirect(""'""&r9c[-1]&""'!lease_start_date""),0)>EOMONTH(INDIRECT(""'""&R9C[-1]&""'!asu_adoption_date""),0),IF(EOMONTH" & _
            "(Current_Month_End,0)=EOMONTH(INDIRECT(""'""&R9C[-1]&""'!Lease_Start_date""),0),ROUND(INDIRECT(""'""&R9C[-1]&""'!Right_of_use_asset_value""),2),0),IF(EOMONTH(Current_Month_End,0)=EOMONTH(INDIRECT(""'""&R9C[-1]&""'!asu_adoption_date""),0),ROUND(INDIRECT(""'""&R9C[-1]&""'!Right_of_use_asset_value""),2),0)),"""")),"""")" & _
            ""
            
            sws.Cells(18, x).FormulaR1C1 = _
            "=IFERROR(IF(EOMONTH(Current_Month_End,-1)<indirect(""'""&r[-9]c&""'!termination_extension_date""),if(and(eomonth(current_month_end,0)<>EOMONTH(INDIRECT(""'""&R[-9]C&""'!asu_adoption_date""),0),EOMONTH(Current_Month_End,0)<>EOMONTH(INDIRECT(""'""&R[-9]C&""'!Lease_Start_date""),0)),ROUND(INDEX(INDIRECT(""'""&R[-9]C&""'!$A$23:$J$1091""),MATCH(R18C1,INDIRECT(""'""&R[-9]" & _
            "C&""'!$B$23:$B$1091""),0),4),2),0),IF(0=INDIRECT(""'""&R[-9]C&""'!Termination_Extension_Date""),IF(AND(EOMONTH(Current_Month_End,0)<>EOMONTH(INDIRECT(""'""&R[-9]C&""'!asu_adoption_date""),0),EOMONTH(Current_Month_End,0)<>EOMONTH(INDIRECT(""'""&R[-9]C&""'!Lease_Start_date""),0)),ROUND(INDEX(INDIRECT(""'""&R[-9]C&""'!$A$23:$J$1091""),MATCH(R18C1,INDIRECT(""'""&R[-9]C&" & _
            """'!$B$23:$B$1091""),0),4),2),0),"""")),"""")" & _
            ""
            
            sws.Cells(19, y).FormulaR1C1 = _
            "=IFERROR(IF(EOMONTH(Current_Month_End,-1)<indirect(""'""&r[-10]c[-1]&""'!termination_extension_date""),if(and(eomonth(current_month_end,0)<>EOMONTH(INDIRECT(""'""&R[-10]C[-1]&""'!asu_adoption_date""),0),EOMONTH(Current_Month_End,0)<>EOMONTH(INDIRECT(""'""&R[-10]C[-1]&""'!Lease_Start_date""),0)),ROUND(INDEX(INDIRECT(""'""&R[-10]C[-1]&""'!$A$23:$J$1091""),MATCH(R18C1," & _
            "INDIRECT(""'""&R[-10]C[-1]&""'!$B$23:$B$1091""),0),4),2),0),IF(0=INDIRECT(""'""&R[-10]C[-1]&""'!Termination_Extension_Date""),IF(AND(EOMONTH(Current_Month_End,0)<>EOMONTH(INDIRECT(""'""&R[-10]C[-1]&""'!asu_adoption_date""),0),EOMONTH(Current_Month_End,0)<>EOMONTH(INDIRECT(""'""&R[-10]C[-1]&""'!Lease_Start_date""),0)),ROUND(INDEX(INDIRECT(""'""&R[-10]C[-1]&""'!$A$23:" & _
            "$J$1091""),MATCH(R18C1,INDIRECT(""'""&R[-10]C[-1]&""'!$B$23:$B$1091""),0),4),2),0),"""")),"""")" & _
            ""
            
            sws.Cells(22, x).FormulaR1C1 = _
            "=IFERROR(IF(EOMONTH(Current_Month_End,-1)<indirect(""'""&r[-13]c&""'!termination_extension_date""),if(and(eomonth(current_month_end,0)<>EOMONTH(INDIRECT(""'""&R[-13]C&""'!asu_adoption_date""),0),EOMONTH(Current_Month_End,0)<>EOMONTH(INDIRECT(""'""&R[-13]C&""'!Lease_Start_date""),0)),IF(INDIRECT(""'""&R[-13]C&""'!Basis_of_Expense"")=""Straight-line"",ROUND(INDEX(INDI" & _
            "RECT(""'""&R[-13]C&""'!$A$23:$J$1091""),MATCH(R18C1,INDIRECT(""'""&R[-13]C&""'!$B$23:$B$1091""),0),10),2),ROUND(INDEX(INDIRECT(""'""&R[-13]C&""'!$A$23:$J$1091""),MATCH(R18C1,INDIRECT(""'""&R[-13]C&""'!$B$23:$B$1091""),0),4),2)),0),IF(0=INDIRECT(""'""&R[-13]C&""'!Termination_Extension_Date""),IF(AND(EOMONTH(Current_Month_End,0)<>EOMONTH(INDIRECT(""'""&R[-13]C&""'!asu" & _
            "_adoption_date""),0),EOMONTH(Current_Month_End,0)<>EOMONTH(INDIRECT(""'""&R[-13]C&""'!Lease_Start_date""),0)),IF(INDIRECT(""'""&R[-13]C&""'!Basis_of_Expense"")=""Straight-line"",ROUND(INDEX(INDIRECT(""'""&R[-13]C&""'!$A$23:$J$1091""),MATCH(R18C1,INDIRECT(""'""&R[-13]C&""'!$B$23:$B$1091""),0),10),2),ROUND(INDEX(INDIRECT(""'""&R[-13]C&""'!$A$23:$J$1091""),MATCH(R18C1," & _
            "INDIRECT(""'""&R[-13]C&""'!$B$23:$B$1091""),0),4),2)),0),"""")),"""")" & _
            ""
            
            sws.Cells(23, y).FormulaR1C1 = _
            "=IFERROR(IFERROR(IF(EOMONTH(Current_Month_End,-1)<indirect(""'""&r[-14]c[-1]&""'!termination_extension_date""),if(and(eomonth(current_month_end,0)<>EOMONTH(INDIRECT(""'""&R[-14]C[-1]&""'!asu_adoption_date""),0),EOMONTH(Current_Month_End,0)<>EOMONTH(INDIRECT(""'""&R[-14]C[-1]&""'!Lease_Start_date""),0)),IF(INDIRECT(""'""&R[-14]C[-1]&""'!Basis_of_Expense"")=""Straight" & _
            "-line"",ROUND(INDEX(INDIRECT(""'""&R[-14]C[-1]&""'!$A$23:$J$1091""),MATCH(R18C1,INDIRECT(""'""&R[-14]C[-1]&""'!$B$23:$B$1091""),0),5),2),ROUND(INDEX(INDIRECT(""'""&R[-14]C[-1]&""'!$A$23:$J$1091""),MATCH(R18C1,INDIRECT(""'""&R[-14]C[-1]&""'!$B$23:$B$1091""),0),5),2)),0),IF(0=INDIRECT(""'""&R[-14]C[-1]&""'!Termination_Extension_Date""),IF(AND(EOMONTH(Current_Month_End" & _
            ",0)<>EOMONTH(INDIRECT(""'""&R[-14]C[-1]&""'!asu_adoption_date""),0),EOMONTH(Current_Month_End,0)<>EOMONTH(INDIRECT(""'""&R[-14]C[-1]&""'!Lease_Start_date""),0)),IF(INDIRECT(""'""&R[-14]C[-1]&""'!Basis_of_Expense"")=""Straight-line"",ROUND(INDEX(INDIRECT(""'""&R[-14]C[-1]&""'!$A$23:$J$1091""),MATCH(R18C1,INDIRECT(""'""&R[-14]C[-1]&""'!$B$23:$B$1091""),0),5),2),ROUND(" & _
            "INDEX(INDIRECT(""'""&R[-14]C[-1]&""'!$A$23:$J$1091""),MATCH(R18C1,INDIRECT(""'""&R[-14]C[-1]&""'!$B$23:$B$1091""),0),5),2)),0),0)),"""")+IFERROR(IF(EOMONTH(Current_Month_End,-1)<indirect(""'""&r[-14]c[-1]&""'!termination_extension_date""),if(and(eomonth(current_month_end,0)<>EOMONTH(INDIRECT(""'""&R[-14]C[-1]&""'!asu_adoption_date""),0),EOMONTH(Current_Month_End,0)<" & _
            ">EOMONTH(INDIRECT(""'""&R[-14]C[-1]&""'!Lease_Start_date""),0)),IF(INDIRECT(""'""&R[-14]C[-1]&""'!Deferred_Rent"")=0,0,-ROUND(INDIRECT(""'""&R[-14]C[-1]&""'!Deferred_Rent"")/COUNTIF(INDIRECT(""'""&R[-14]C[-1]&""'!$B$29:$B$1098""),"">=""&INDIRECT(""'""&R[-14]C[-1]&""'!ASU_Adoption_Date"")),2)),0),IF(0=INDIRECT(""'""&R[-14]C[-1]&""'!Termination_Extension_Date""),IF(EO" & _
            "MONTH(Current_Month_End,0)<>EOMONTH(INDIRECT(""'""&R[-14]C[-1]&""'!asu_adoption_date""),0),IF(INDIRECT(""'""&R[-14]C[-1]&""'!Deferred_Rent"")=0,0,-ROUND(INDIRECT(""'""&R[-14]C[-1]&""'!Deferred_Rent"")/COUNTIF(INDIRECT(""'""&R[-14]C[-1]&""'!$B$29:$B$1098""),"">=""&INDIRECT(""'""&R[-14]C[-1]&""'!ASU_Adoption_Date"")),2)),0),0)),""""),"""")" & _
            ""
            
            sws.Cells(25, y).FormulaR1C1 = _
            "=IFERROR(IF(EOMONTH(Current_Month_End,-1)<indirect(""'""&r[-16]c[-1]&""'!termination_extension_date""),if(and(eomonth(current_month_end,0)<>EOMONTH(INDIRECT(""'""&R[-16]C[-1]&""'!asu_adoption_date""),0),EOMONTH(Current_Month_End,0)<>EOMONTH(INDIRECT(""'""&R[-16]C[-1]&""'!Lease_Start_date""),0)),IF(INDIRECT(""'""&R[-16]C[-1]&""'!Basis_of_Expense"")=""Straight-line""," & _
            "ROUND(INDEX(INDIRECT(""'""&R[-16]C[-1]&""'!$A$23:$J$1091""),MATCH(R18C1,INDIRECT(""'""&R[-16]C[-1]&""'!$B$23:$B$1091""),0),7),2),ROUND(INDEX(INDIRECT(""'""&R[-16]C[-1]&""'!$A$23:$J$1091""),MATCH(R18C1,INDIRECT(""'""&R[-16]C[-1]&""'!$B$23:$B$1091""),0),7),2)),0),IF(0=INDIRECT(""'""&R[-16]C[-1]&""'!Termination_Extension_Date""),IF(AND(EOMONTH(Current_Month_End,0)<>EOM" & _
            "ONTH(INDIRECT(""'""&R[-16]C[-1]&""'!asu_adoption_date""),0),EOMONTH(Current_Month_End,0)<>EOMONTH(INDIRECT(""'""&R[-16]C[-1]&""'!Lease_Start_date""),0)),IF(INDIRECT(""'""&R[-16]C[-1]&""'!Basis_of_Expense"")=""Straight-line"",ROUND(INDEX(INDIRECT(""'""&R[-16]C[-1]&""'!$A$23:$J$1091""),MATCH(R18C1,INDIRECT(""'""&R[-16]C[-1]&""'!$B$23:$B$1091""),0),7),2),ROUND(INDEX(IN" & _
            "DIRECT(""'""&R[-16]C[-1]&""'!$A$23:$J$1091""),MATCH(R18C1,INDIRECT(""'""&R[-16]C[-1]&""'!$B$23:$B$1091""),0),7),2)),0),"""")),"""")" & _
            ""
            
            sws.Cells(29, x).FormulaR1C1 = _
            "=IFERROR(IF(EOMONTH(R29C1,0)=EOMONTH(INDIRECT(R9C&""!Termination_Extension_Date""),0),ROUND(INDIRECT(R9C&""!$B$23""),2),""""),"""")"
            
            sws.Cells(30, x).FormulaR1C1 = _
            "=IFERROR(IF(EOMONTH(R29C1,0)=EOMONTH(INDIRECT(R9C&""!Termination_Extension_Date""),0),ROUND(INDIRECT(R9C&""!$B$22""),2),""""),"""")"
            
            sws.Cells(31, y).FormulaR1C1 = _
            "=IFERROR(IF(EOMONTH(R29C1,0)=EOMONTH(INDIRECT(R9C[-1]&""!Termination_Extension_Date""),0),ROUND(INDIRECT(R9C[-1]&""!Right_of_use_asset_value""),2),""""),"""")"
            
            sws.Cells(34, x).FormulaR1C1 = _
            "=IFERROR(IF(EOMONTH(R29C1,0)=EOMONTH(INDIRECT(R9C&""!Termination_Extension_Date""),0),ROUND(INDIRECT(R9C&""!$B$21""),2),""""),"""")"
            
            sws.Cells(35, y).FormulaR1C1 = _
            "=IFERROR(IF(EOMONTH(R29C1,0)=EOMONTH(INDIRECT(R9C[-1]&""!Termination_Extension_Date""),0),ROUND(INDIRECT(R9C[-1]&""!$B$21""),2),""""),"""")"
            
        x = x + 2
        y = y + 2
    End If
Next ws


        sws.Cells(9, x) = "Summary"
        sws.Range(Cells(9, x), Cells(9, x + 1)).Merge
        sws.Range(Cells(9, x), Cells(10, x + 1)).HorizontalAlignment = xlCenter
        sws.Cells(10, x) = "Debit"
        sws.Cells(10, y) = "Credit"
        
        sws.Cells(12, x).FormulaR1C1 = "=SUMIF(R10C3:R10C[-1],R[-2]C15,R12C3:R12C[-1])"
        
        sws.Cells(12, y).FormulaR1C1 = "=SUMIF(R10C3:R10C[-1],R[-2]C16,R12C3:R12C[-1])"
        
        sws.Cells(13, x).FormulaR1C1 = "=SUMIF(R[-3]C3:R10C[-1],R10C,RC3:R13C[-1])"
        
        sws.Cells(13, y).FormulaR1C1 = "=SUMIF(R10C3:R10C[-1],R10C,R13C3:R13C[-1])"
        
        sws.Cells(14, x).FormulaR1C1 = "=SUMIF(R[-4]C3:R10C[-1],R10C,RC3:R14C[-1])"
        
        sws.Cells(14, y).FormulaR1C1 = "=SUMIF(R10C3:R10C[-1],R[-4]C16,R14C3:R14C[-1])"
        
        sws.Cells(18, x).FormulaR1C1 = "=SUMIF(R10C3:R10C[-1],R10C,R18C3:R18C[-1])"
        
        sws.Cells(18, y).FormulaR1C1 = "=SUMIF(R10C3:R10C[-1],R10C,R18C3:R18C[-1])"
        
        sws.Cells(19, x).FormulaR1C1 = "=SUMIF(R10C3:R10C[-1],R10C,R19C3:R19C[-1])"
        
        sws.Cells(19, y).FormulaR1C1 = "=SUMIF(R10C3:R10C[-1],R10C,R19C3:R19C[-1])"
        
        sws.Cells(22, x).FormulaR1C1 = "=SUMIF(R10C3:R10C[-1],R10C,R22C3:R22C[-1])"
        
        sws.Cells(22, y).FormulaR1C1 = "=SUMIF(R10C3:R10C[-1],R10C,R22C3:R22C[-1])"
        
        sws.Cells(23, x).FormulaR1C1 = "=SUMIF(R10C3:R10C[-1],R10C,R23C3:R23C[-1])"
        
        sws.Cells(23, y).FormulaR1C1 = "=SUMIF(R10C3:R10C[-1],R10C,R23C3:R23C[-1])"
        
        sws.Cells(24, x).FormulaR1C1 = "=SUMIF(R10C3:R10C[-1],R10C,R24C3:R24C[-1])"
        
        sws.Cells(24, y).FormulaR1C1 = "=SUMIF(R10C3:R10C[-1],R10C,R24C3:R24C[-1])"
        
        sws.Cells(25, x).FormulaR1C1 = "=SUMIF(R10C3:R10C[-1],R10C,R25C3:R25C[-1])"
        
        sws.Cells(25, y).FormulaR1C1 = "=SUMIF(R10C3:R10C[-1],R10C,R25C3:R25C[-1])"
        
        sws.Cells(29, x).FormulaR1C1 = "=SUMIF(R10C3:R10C[-1],R10C,R29C3:R29C[-1])"
        
        sws.Cells(29, y).FormulaR1C1 = "=SUMIF(R10C3:R10C[-1],R10C,R29C3:R29C[-1])"
        
        sws.Cells(30, x).FormulaR1C1 = "=SUMIF(R10C3:R10C[-1],R10C,R30C3:R30C[-1])"
        
        sws.Cells(30, y).FormulaR1C1 = "=SUMIF(R10C3:R10C[-1],R10C,R30C3:R30C[-1])"
        
        sws.Cells(31, x).FormulaR1C1 = "=SUMIF(R10C3:R10C[-1],R10C,R31C3:R31C[-1])"
        
        sws.Cells(31, y).FormulaR1C1 = "=SUMIF(R10C3:R10C[-1],R10C,R31C3:R31C[-1])"
        
        sws.Cells(34, x).FormulaR1C1 = "=SUMIF(R10C3:R10C[-1],R10C,R25C3:R25C[-1])"
        
        sws.Cells(34, y).FormulaR1C1 = "=SUMIF(R10C3:R10C[-1],R10C,R34C3:R34C[-1])"
        
        sws.Cells(35, x).FormulaR1C1 = "=SUMIF(R10C3:R10C[-1],R10C,R25C3:R25C[-1])"
        
        sws.Cells(35, y).FormulaR1C1 = "=SUMIF(R10C3:R10C[-1],R10C,R35C3:R35C[-1])"


Application.ScreenUpdating = True


 
End Sub
</indirect(""'""&r[-16]c[-1]&""'!termination_extension_date""),if(and(eomonth(current_month_end,0)<></indirect(""'""&r[-14]c[-1]&""'!termination_extension_date""),if(and(eomonth(current_month_end,0)<></indirect(""'""&r[-14]c[-1]&""'!termination_extension_date""),if(and(eomonth(current_month_end,0)<></indirect(""'""&r[-13]c&""'!termination_extension_date""),if(and(eomonth(current_month_end,0)<></indirect(""'""&r[-10]c[-1]&""'!termination_extension_date""),if(and(eomonth(current_month_end,0)<></indirect(""'""&r[-9]c&""'!termination_extension_date""),if(and(eomonth(current_month_end,0)<></indirect(""'""&r[-5]c[-1]&""'!termination_extension_date""),if(eomonth(indirect(""'""&r9c[-1]&""'!lease_start_date""),0)></indirect(""'""&r[-4]c&""'!termination_extension_date""),if(eomonth(indirect(""'""&r9c&""'!lease_start_date""),0)></indirect(""'""&r[-3]c&""'!termination_extension_date""),if(eomonth(indirect(""'""&r9c&""'!lease_start_date""),0)>
 
The formula approach that you use is a pretty efficient way to do things.

Calculating formulas in cells is a ton faster than making the same calculation in VBA.
 
Upvote 0

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