embedding a long formula in VBA

liquid

New Member
Joined
Sep 8, 2004
Messages
48
I have a very large 'database' spreadsheet that stores details of about 150 deals and works out an amortization profile for them by month for the next 20 years. As a result the spreasheet is 150 rows by 216 columns.

In each cell of that grid I have the following formula which calculates whether part of the deal is maturing in the month and if so how much.

Obviously having this formula in there so many times has caused the file size to grow, it is now over 15MB.

=IF(RIGHT($D148,2)="CB",IF(EOMONTH($M148,0)=HP$6,$H148,""),(IF(MONTH(EOMONTH($K148,0))<>MONTH(HP$6),"",(IF(OR((YEARFRAC(EOMONTH($K148,0),HP$6,0))<1,((YEARFRAC(EOMONTH($K148,0),HP$6,0))>COLUMNS(amortization)-1),(YEAR(EOMONTH($K148,0))>YEAR(HP$6))),"",IF(VLOOKUP($D148,amortization,ROUND(YEARFRAC(EOMONTH($K148,0),HP$6,0),0)+1,FALSE)=0%,"",VLOOKUP($D148,amortization,ROUND(YEARFRAC(EOMONTH($K148,0),HP$6,0),0)+1,FALSE)*$H148))))))

I would like to embed this formula in the VBA editor so that it still does exactly what it does now and returns a value in each cell where it should, but I don't want it taking up so much space!

Is there any way I can do this??

Thanks
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,772
The below example assumes your data starts in column A, and you would like the above formula in column B (change the code as appropriate).

Note, there may be more efficient methods.

Code:
Sub formula()
Dim End_Row as Long

Application.ScreenUpdating = False

End_Row = Range("A65536").End(xlUp).Row

Range("B2").Formula = "=IF(RIGHT($D148,2)=""CB"",IF(EOMONTH($M148,0)=HP$6,$H148,""""),(IF(MONTH(EOMONTH($K148,0))<>MONTH(HP$6),"""",(IF(OR((YEARFRAC(EOMONTH($K148,0),HP$6,0))<1,((YEARFRAC(EOMONTH($K148,0),HP$6,0))>COLUMNS(amortization)-1),(YEAR(EOMONTH($K148,0))>YEAR(HP$6))),"""",IF(VLOOKUP($D148,amortization,ROUND(YEARFRAC(EOMONTH($K148,0),HP$6,0),0)+1,FALSE)=0%,"""",VLOOKUP($D148,amortization,ROUND(YEARFRAC(EOMONTH($K148,0),HP$6,0),0)+1,FALSE)*$H148))))))"

Range("B2").Copy Destination:=Range("B3:B" & End_Row)
With Range("B2:B" & End_Row)
.Copy
.PasteSpecial xlValues
End With

Application.CutCopyMode = False

Application.ScreenUpdating = True

End Sub
 

gallan

Board Regular
Joined
May 27, 2007
Messages
79
Hi

How about a subroutine, that finds the value of the cells without pasting in the formula

Assume X is the name of the sheet
Assume basecell is the first cell in you field of 150 by 216 cells

Presumably the references must change for each cell referred to in your formula
Something like sheets(“X”).[D148].offset(j,0) to always stay in the D column but to move down from row 148

For k = 0 to 150
For j = 0 to 216

IF(RIGHT($D148,2)="CB" then
IF(EOMONTH($M148,0)=HP$6 then
Val = $H148
Else
val = ""
endif
else
IF(MONTH(EOMONTH($K148,0))<>MONTH(HP$6) then
Val = ""
Else
IF ((YEARFRAC(EOMONTH($K148,0),HP$6,0))<1 OR ((YEARFRAC(EOMONTH($K148,0),HP$6,0))>COLUMNS(amortization)-1) OR (YEAR(EOMONTH($K148,0))>YEAR(HP$6))) then

Val=""
Else
IF(VLOOKUP($D148,amortization,ROUND(YEARFRAC(EOMONTH($K148,0),HP$6,0),0)+1,FALSE)=0% then
Val = ""
Else
Val = vLOOKUP($D148,amortization,ROUND(YEARFRAC(EOMONTH($K148,0),HP$6,0),0)+1,FALSE)*$H148))))))
Endif
Endif
Endif
Endif

Sheets (“X”).[basecell].offset(j,k)

Next
Next

Obviously what I have written is not complete or correct ( I find it difficult to decipher long Excel formulae) but it is on a track to solve the problem I think. And I wouldn't format it like it is here
 

Forum statistics

Threads
1,181,363
Messages
5,929,538
Members
436,676
Latest member
Mavri

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