Copying formulas to range

[Yoopergirl1]

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.

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

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]

I see in your code where you are suspending screen updating here:

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:

Application.ScreenUpdating = True

to turn it back on.

 

[Yoopergirl1]

I see in your code where you are suspending screen updating here:

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:

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]

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]

For the sake of testing, you can temporarily replace:

Application.ScreenUpdating = False

with

Application.ScreenUpdating =True

It may slow down your code a little, but then we can confirm whether that is involved in the issue.

 

[Yoopergirl1]

For the sake of testing, you can temporarily replace:

Application.ScreenUpdating = False

with

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]

Try these two tests. After the:

First Test
Before the line in the block of the code in your first post:

Range("Formulas").Select

place this line:

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.

Application.CutCopyMode = False

line in your original code, add this line:

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]

Try these two tests. After the:

First Test
Before the line in the block of the code in your first post:

Range("Formulas").Select

place this line:

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.

Application.CutCopyMode = False

line in your original code, add this line:

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]

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]

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]

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?