# embedding a long formula in VBA

#### liquid

##### New Member
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
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
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

Replies
9
Views
654
Replies
0
Views
46
Replies
6
Views
439
Replies
0
Views
578
Replies
4
Views
151

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.

### Which adblocker are you using?

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

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