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

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

As it is now, doesn't paste the working formula, just the data that shows.
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?
 

Yoopergirl1

New Member
Joined
Feb 4, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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?
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?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
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.
 

Yoopergirl1

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

ADVERTISEMENT

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

Yoopergirl1

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

ADVERTISEMENT

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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
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.
What were your results?
 

Yoopergirl1

New Member
Joined
Feb 4, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Did you try any of these things here?

What were your results?
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
 

Watch MrExcel Video

Forum statistics

Threads
1,126,986
Messages
5,621,992
Members
415,873
Latest member
fuulhouse

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