Copying formulas to range

Yoopergirl1

New Member
Joined
Feb 4, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
I've tried looking in the forum to see if I can find a solution, but nothing seems to work. Hopefully someone can help me figure this out. I am taking an existing spreadsheet and updating it to be what we currently need. I had to insert a few columns into the spreadsheet, which in turn, made me adjust all the code in VBA. Everything seems to work, except when I use the code to copy a named formula down into the table. The number of rows in the table is not static, it will change with each issue that we do. The named formulas are in row 1, columns E-G. The formula's have to be pasted, and work in row 17, column E-G and down to as many rows as there are data. As it is now, doesn't paste the working formula, just the data that shows.

This is the spreadsheet - formula for E1 is =1/(1+Rate/AnnCompound)^-(Days360(A1,DateFuture)/(360/AnnCompound), F1 - D1*E1, G1 - =Days360(A1,DateFuture)/(360/AnnCompound). DateFuture is the same as the "Future Value To" date.
1612454887106.png


and the code that used to work, but adjusted for the new spreadsheet ("Start" = A16 and "Formulas" = E1:G1)

1612454765127.png

I hope i'm doing this right, I've never asked for help here before. Hopefully someone can help me fix my problem! Thank you!!!!!!!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,552
Office Version
  1. 365
Platform
  1. Windows
I see in your code where you are suspending screen updating here:
Rich (BB code):
Sub CollectData()
Application.ScreenUpdating = False
but I don't see in your code anywhere where you are turning it back on.

You should have a line like this at the end of your code:
Rich (BB code):
Application.ScreenUpdating = True
to turn it back on.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Yoopergirl1

New Member
Joined
Feb 4, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
I see in your code where you are suspending screen updating here:
Rich (BB code):
Sub CollectData()
Application.ScreenUpdating = False
but I don't see in your code anywhere where you are turning it back on.

You should have a line like this at the end of your code:
Rich (BB code):
Application.ScreenUpdating = True
to turn it back on.
I tried putting it in a few different places and it doesn't seem to work anywhere. Where do you suggest to try it??
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,552
Office Version
  1. 365
Platform
  1. Windows
I tried putting it in a few different places and it doesn't seem to work anywhere. Where do you suggest to try it??
Before every "End Sub" line, and any other place it might exit your code before then.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,552
Office Version
  1. 365
Platform
  1. Windows
For the sake of testing, you can temporarily replace:
VBA Code:
Application.ScreenUpdating = False
with
VBA Code:
Application.ScreenUpdating =True
It may slow down your code a little, but then we can confirm whether that is involved in the issue.
 

Yoopergirl1

New Member
Joined
Feb 4, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

For the sake of testing, you can temporarily replace:
VBA Code:
Application.ScreenUpdating = False
with
VBA Code:
Application.ScreenUpdating =True
It may slow down your code a little, but then we can confirm whether that is involved in the issue.
Tried what you suggested. I did slow it way down, but it didn't solve the problem :(
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,552
Office Version
  1. 365
Platform
  1. Windows
Try these two tests. After the:

First Test
Before
the line in the block of the code in your first post:
VBA Code:
Range("Formulas").Select
place this line:
VBA Code:
Exit Sub

Then run your code, and look at the cells in your "Formulas" range (E1:G1).
Are they formulas or hard-coded values?

Second Test
Remove the line you added above.
After this line in the block of code in your first post.
VBA Code:
Application.CutCopyMode = False
line in your original code, add this line:
VBA Code:
Exit Sub

Run that, and take a look at the data that was copied.
If you select any of the new copied cells, does it show a formula or a hard-coded value?
 

Yoopergirl1

New Member
Joined
Feb 4, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try these two tests. After the:

First Test
Before
the line in the block of the code in your first post:
VBA Code:
Range("Formulas").Select
place this line:
VBA Code:
Exit Sub

Then run your code, and look at the cells in your "Formulas" range (E1:G1).
Are they formulas or hard-coded values?

Second Test
Remove the line you added above.
After this line in the block of code in your first post.
VBA Code:
Application.CutCopyMode = False
line in your original code, add this line:
VBA Code:
Exit Sub

Run that, and take a look at the data that was copied.
If you select any of the new copied cells, does it show a formula or a hard-coded value?
It was really really really slow, and no I don't have the ScreenUpdating in there anymore. When I finally got through the code, the Factor and Future Value are hard-coded, but Periods is now a formula.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,552
Office Version
  1. 365
Platform
  1. Windows
It was really really really slow, and no I don't have the ScreenUpdating in there anymore. When I finally got through the code, the Factor and Future Value are hard-coded, but Periods is now a formula
Which test are you talking about? This first or the second?
Please read each set of instructions carefully, and tell me the results of each test.
If you want me to help you, you need to follow these steps exactly, and tell me the exact results.
Unless you can provide us with the file, we are relying on your to do all of this.

BTW, you can set the ScreenUpdating back now to what it was in the beginning. I don't think that is the issue.
 

Yoopergirl1

New Member
Joined
Feb 4, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Which test are you talking about? This first or the second?
Please read each set of instructions carefully, and tell me the results of each test.
If you want me to help you, you need to follow these steps exactly, and tell me the exact results.
Unless you can provide us with the file, we are relying on your to do all of this.

BTW, you can set the ScreenUpdating back now to what it was in the beginning. I don't think that is the issue.
Sorry, that was the second test. I was interrupted with work while doing it and forgot to tell you that with the first test, there was no change
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,552
Office Version
  1. 365
Platform
  1. Windows
Any chance you can upload a copy of the workbook, with any sensitive data removed, to a file sharing site for us to look at?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,222
Messages
5,623,472
Members
415,973
Latest member
johnemaile

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