Copying formulas to range

Y

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!!!!!!!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Welcome to the Board!

Yoopergirl1 said:
As it is now, doesn't paste the working formula, just the data that shows.
Click to expand...
Your code works fine for me.
It sounds like you may have your worksheet set to manual calculation mode.
What happens if after running it, your press F9?
 
Upvote 0
Joe4 said:
Welcome to the Board!


Your code works fine for me.
It sounds like you may have your worksheet set to manual calculation mode.
What happens if after running it, your press F9?
Click to expand...
No it is not in manual mode. I even tried it just to make sure ;) Maybe something else in the code is affecting it?? Any idea how I can figure it out?
 
Upvote 0
Did you try stepping through your code?
Or putting a break point in the code at the "Application.CutCopyMode = False" line.
Then when you run your code, it will stop at that line. Then look at your sheet and see what the data looks like.
If it looks correct at that point, it means something else in your code after that point is overwriting it. You can go through the rest of the code line-by-line, using F8, and see where the data changes.

You can also post the rest of your VBA code here for analysis.
 
Upvote 0
Joe4 said:
Did you try stepping through your code?
Or putting a break point in the code at the "Application.CutCopyMode = False" line.
Then when you run your code, it will stop at that line. Then look at your sheet and see what the data looks like.
If it looks correct at that point, it means something else in your code after that point is overwriting it. You can go through the rest of the code line-by-line, using F8, and see where the data changes.

You can also post the rest of your VBA code here for analysis.
Click to expand...
Sorry, I'm new to this forum. There is a lot of code for that page. How do I post the reset of it?? Do I just copy it and dump it all in here? Thank you for trying to help!! I really appreciate it!
 
Upvote 0
Ok, I think I did this right.... It's not pretty... .the guy who wrote it, did enough so it would work. I hope to eventually clean it up.

VBA Code: 
Sub CollectData()
Application.ScreenUpdating = False

Sheets("Report").Select


    Range(Range("Start").Offset(1, 0), _
        Range("Start").Offset(1, 0).End(xlDown).End(xlDown).Offset(0, 6)).Select
    Selection.ClearContents
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlNone
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        
    
Sheets("Data").Select
Range("DateBegin").Activate

Range(ActiveCell.Offset(1, 0), ActiveCell.Offset.End(xlDown).Offset(0, 3)).Select
Selection.Copy


Sheets("Report").Select
Range("Start").Offset(1, 0).Select
 'ActiveSheet.Paste
    'Application.CutCopyMode = False
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    'Do Until ActiveCell.Offset(-1, 0).Value = Range("DateFuture").Value
    'Do Until ActiveCell.Value = 0
    '    ActiveCell.Offset(1, 0).Activate
    'If ActiveCell.Offset(0, 1).Value <> 0 Then
    '    ActiveWorkbook.Names.Add Name:="DateData", RefersToR1C1:=Selection
    '    Insert
    '    Sheets("Data").Select
    '    Range("DateData").Activate
    '    ActiveWorkbook.Names("DateData").Delete
    'End If
    'Loop


CopyFormulas

    Range(Range("Start"), Range("Start").Offset(0, 5)).Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        '.TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        '.TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        '.TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        '.TintAndShade = 0
        .Weight = xlThin
    End With
    
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        Range("F19").Select

    Range(Range("Start"), Range("Start").Offset(0, 5)).Select
    With Selection.Borders(xlEdgeBottom)
       .LineStyle = xlContinuous
       .Weight = xlThin
      .ColorIndex = xlAutomatic
    End With

    Range(Range("Start").Offset(1, 0), _
        Range("Start").Offset(1, 0).End(xlDown).End(xlDown).Offset(0, 5)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False


    Range(Range("Start").Offset(1, 3), _
        Range("Start").Offset(1, 0).End(xlDown).End(xlDown).Offset(0, 3)).Select
    Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"

    Range(Range("Start").Offset(1, 5), _
        Range("Start").Offset(1, 0).End(xlDown).End(xlDown).Offset(0, 5)).Select
    Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
    
    Range(Range("Start").Offset(1, 0), Range("Start").Offset(1, 0).End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        .NumberFormat = "m/d/yyyy"
        .HorizontalAlignment = xlRight
    End With
    
    Range(Range("Start").Offset(1, 2), _
        Range("Start").Offset(1, 0).End(xlDown).End(xlDown).Offset(0, 2)).Select
    With Selection
       .HorizontalAlignment = xlLeft
    End With

     Range(Range("Start").Offset(1, 1), _
        Range("Start").Offset(1, 0).End(xlDown).End(xlDown).Offset(0, 1)).Select
    With Selection
       .HorizontalAlignment = xlCenter
    End With

    Range(Range("Start").Offset(1, 3), _
        Range("Start").Offset(1, 0).End(xlDown).End(xlDown).Offset(0, 3)).Select
    With Selection
       .HorizontalAlignment = xlRight
    End With

    Range(Range("Start").Offset(1, 4), _
        Range("Start").Offset(1, 0).End(xlDown).End(xlDown).Offset(0, 4)).Select
    With Selection
       .HorizontalAlignment = xlCenter
    End With

   Range(Range("Start").Offset(1, 5), _
        Range("Start").Offset(1, 0).End(xlDown).End(xlDown).Offset(0, 5)).Select
    With Selection
       .HorizontalAlignment = xlRight
    End With

     Range(Range("Start").Offset(1, 0), _
       Range("Start").Offset(1, 0).End(xlDown).End(xlDown).Offset(0, 6)).Select
    With Selection.Font
        .Name = "Arial Nova Cond"
       .Size = 10
    End With
    
    
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        '.TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        '.TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        '.TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        '.TintAndShade = 0
        .Weight = xlThin
    End With
    
    
    
    
    
    Range("Start").Offset(1, 0).Activate
    Do Until ActiveCell.Value = 0
    ActiveCell.Offset(1, 0).Activate
    If Abs(ActiveCell.Offset(-1, 3).Value) < 0.005 Then
        ActiveCell.Offset(-1, 0).Select
        Selection.EntireRow.Delete
        'ActiveCell.Offset(1, 0).Activate
    End If
    Loop

Range("Start").Activate

End Sub
Sub Insert()
Sheets("Report").Select
Range("Start").Activate
Do Until ActiveCell.Value = 0
ActiveCell.Offset(1, 0).Activate
Loop

ActiveCell.Value = Range("DateData").Value
ActiveCell.Offset(0, 1).Value = Range("DateData").Offset(0, 1).Value

End Sub
Sub CopyFormulas()

Sheets("Report").Select
Dim FVTotalRange As Range
Dim RDTotalRange As Range

    'If Name.exists("FVTotalRange") Then ActiveWorkbook.Names("FVTotalRange").Delete


    'Copy formulas from Row 1 and paste into data table
    Range("Formulas").Select
    Selection.Copy
    Range(Range("Start").Offset(1, 4), Range("Start").Offset(1, 0).End(xlDown).Offset(0, 6)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    

    
    Range("Start").End(xlDown).Offset(2, 0).Activate
    ActiveCell.Value = "TOTAL"
    
    ActiveCell.Offset(0, 5).Activate
    
    Set FVTotalRange = Range(ActiveCell.Offset.End(xlUp).End(xlUp).Offset(1, 0), ActiveCell.Offset(-2, 0))
    FVTotalRange.Select
    ActiveWorkbook.Names.Add Name:="FVTotalRange", RefersToR1C1:=Selection
    Range("Start").Offset.End(xlDown).End(xlDown).Offset(0, 5).Activate
   
    ActiveCell.FormulaR1C1 = "=SUM(fvtotalrange)"
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Range("FVTotalReport").Value = ActiveCell.Value
    
    
    Set RDTotalRange = Range(Range("Start").Offset(1, 3), Range("Start").Offset(1, 3).End(xlDown))
    RDTotalRange.Select
    ActiveWorkbook.Names.Add Name:="RDTotalRange", RefersToR1C1:=Selection
    Range("Start").Offset.End(xlDown).End(xlDown).Offset(0, 3).Activate
   
    ActiveCell.FormulaR1C1 = "=SUM(rdtotalrange)"
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    
End Sub
 
Upvote 0
Did you try any of these things here?
Did you try stepping through your code?
Or putting a break point in the code at the "Application.CutCopyMode = False" line.
Then when you run your code, it will stop at that line. Then look at your sheet and see what the data looks like.
If it looks correct at that point, it means something else in your code after that point is overwriting it. You can go through the rest of the code line-by-line, using F8, and see where the data changes.
Click to expand...
What were your results?
 
Upvote 0
Upvote 0
Joe4 said:
Did you try any of these things here?

What were your results?
Click to expand...
This is before the code to copy the formulas.....
1612543240632.png


After formulas are copied....
1612543307588.png


The "Factor" "Future Value" and "Period" columns should look similar to this... (same spreadsheet before the other 2 columns were added in).

1612543394731.png
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Working with a formula that pulls data from multiple columns
2
Replies
16
Views
699
KRice
K
A
  • Solved
Fill down formulas and Do Until "No more data"
Replies
4
Views
734
felixstraube
F
J
  • Solved
Copying the data from another spreadsheet for specific product
Replies
2
Views
120
junaidaun
J
itsgrady
  • Solved
Spill Error - need help with Unique
Replies
2
Views
170
itsgrady
itsgrady
M
  • Locked
  • Question
Automatically Copy Row Based on Cell Value to New Tab
Replies
1
Views
405
Fluff
Fluff

Forum statistics

Threads
1,214,583
Messages
6,120,378
Members
448,955
Latest member
BatCoder

Share this page

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